itsmatta (4/12/2011)
All 4 works correctly and I have selected only 2 as per the question.. So I am right and need my point back
No, all 4 do not work correctly everywhere (i.e. irrespective of date format and/or language settings). DATETIME and SMALLDATETIME are affected by the format.
Per Books On Line http://msdn.microsoft.com/en-us/library/ms189491.aspx,
The effect of the DATEFORMAT setting on the interpretation of character strings might be different for datetime and smalldatetime values than for date, datetime2 and datetimeoffset values, depending on the string format.
Thanks & Regards,
Nakul Vachhrajani.
http://nakulvachhrajani.com
Follow me on
Twitter: @sqltwins