Toreador (10/27/2010)
Hugo Kornelis (10/27/2010)
For the datetime data type, The only formats that are guaranteed to work correctly in all circumstances are:* yyyymmdd - for dates without time portion. No dashes. dots, slashes or other punctuation. The resulting datetime value will have its time portion set to midnight.
We use yyyy-mm-dd which has always worked so far.
Should I be worried?!
yyyy-mm-dd is not universal.
print 'Format mdy'
set dateformat mdy
select x1 =convert(datetime,'2010-12-31')
go
print 'Format ymd'
set dateformat ydm
select x2=convert(datetime,'2010-12-31')
Results:
Format mdy
x1
------------------------
2010-12-31 00:00:00.000
(1 row(s) affected)
Format ymd
Server: Msg 242, Level 16, State 3, Line 3
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.