SQL Server Performance Forum – Threads Archive

Date Convert Issue

Hi, I am having a table with one date field [DOJ] which has a mixed format format, that is some of the records are in mm/dd/yy and some are dd/mm/yy. I need to convert the entire date [DOJ] to dd/mm/yy format. Can any one give the idea for how to do it ? Also, If the date is stored in dd/mm/yy how can I convert the same in to MM/DD/YY format
i tried [Select convert(datetime,Todate,101) from Temp_Table], Temp_Table having data in dd/mm/yy format but failed, since it is showing out-of-range datetime value. Thanks
Ajith

I have a question, in your data dates like 1/7/2007 is it July 1st or January 7th. —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

The fomat is like 1/7/2007 [dd/mm/yyyy]

I resolved the issue by using the ‘SET DATEFORMAT dmy;’ command Please tell whether any other solution for resolving the first point ??

1 Always use proper DATETIME datatype
2 When querying always use ISO format YYYY-MM-DD
3 Let front end do the convertion Madhivanan Failing to plan is Planning to fail

you can convert function as well —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

The data whatt I spoke is already in the data base…is a legacy system, so i canot do it in front end like ‘Madhivanan’ said…I am expecting a solution for the issue…not an advice..!!

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by AJITH123 The data whatt I spoke is already in the data base…is a legacy system, so i canot do it in front end like ‘Madhivanan’ said…I am expecting a solution for the issue…not an advice..!! <hr height="1" noshade id="quote"></blockquote id="quote">If the datatype is not DATETIME then there is no solution except advice [<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>