how to convert/format date format in sql server

Networks

Collapse -

SQL Server Date Formats

by Peconet Tietokoneet · 11 years ago In reply to how to convert/format da ...

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

Collapse -

Formating only works on date types

by Tony Hopkinson · 11 years ago In reply to how to convert/format da ...

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.

Collapse -

Formating only works on date types

by jazzygodfrey · 11 years ago In reply to Formating only works on d ...

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.

Collapse -

Always use a universal

by Tony Hopkinson · 11 years ago In reply to Formating only works on d ...

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.

Networks

Question