Networks·41,388 discussions

how to convert/format date format in sql server

Locked

hi im using sql 2005, I have a varchar column with values in the date format as
21-09-1982 meaning 21-sep-1982
23-05-1981 ” 23-may-1981
25-12-1972 ” 25-dec-1972

I want to format them to read in the format 21-sep-1982 (dd-mmm-yyy)
but it seems my sql server reads them as mm-dd-yyyy when i try to use convert or cast
it says the conversion resulted in an out of range value.
How can i format my sql to read them as dd-mmm-yyyy?

Topic

Clarifications

In reply to how to convert/format date format in sql server

Clarifications

SQL Server Date Formats

In reply to how to convert/format date format in sql server

Get Date Only Function:
http://www.sql-server-helper.com/functions/get-date-only.aspx

SQL Server Date Formats
http://www.sql-server-helper.com/tips/date-formats.aspx

Formating only works on date types

In reply to how to convert/format date format in sql server

and it will generally work by locale. Unfortuately as MS is designed in the US, it often as a default or through error drops into us format, so I personally never trust it in this sort of problem.

Best bet is to rearrange the varchar in a universal format (YYYY-MM-DD) and then convert that to a date and then convert it to the desired format on output.

This is going to be messy

Select SubString([DuffDate],7,4) + ‘-‘ + SubString([DuffDate],4,2) + ‘-‘ + SubString([DuffDate],1,2) From DuffDates

Would give you a string containing universal formatted date, so

Select Convert(DateTime,SubString([DuffDate],7,4) + ‘-‘ + SubString([DuffDate],4,2) + ‘-‘ + SubString([DuffDate],1,2)) From DuffDates

Gives you a dateTime and then wrap that in convert for teh required formatting.

This is the sort of problem you should fix at source if you can and make this naff column a date.

Another option might be to add a datatime column to it and then do an UPdate … Where ProperDate is Null before processing.

Formating only works on date types

In reply to Formating only works on date types

Thanks Tony
I Actually tried option 2 before i read you post, and it did work for me, although I tried option one before but not in a universal formating date like yours because at first the date was in string like ‘11052010’ so i had to use the substing function to rearrange in date formart like 11-05-2010 and later used the option two of yours.

But anyways you gave me the best and straight foward solution im gonna use next time.
Thanx a million Tony.

Always use a universal

In reply to Formating only works on date types

for passing dates as strings or integers.
NB if toy pass them as floats, don’t forget different systems can have different epochs.

Always, no exceptions. If the source or sink need different format they should take care of their own, any other way is just an issue waiting to be discovered.

I’ve seen some real head scratchers in my time, especially in windows api routines.

My all time favourite was one with some logic in it to guess between MM/DD and DD/MM. So as soon as you got past the 12th it worked but before that it picked the short date format in regional settings. Given it was a ‘time stamp’ function it all worked when we tested it,

at the end of the month. 🙁 🙁

[Author]

Replies