SQLTeam.com | Weblogs | Forums

Date data type


#1

Hi,

From the MSDN Library "https://msdn.microsoft.com/en-ca/library/bb630352.aspx" i am reading about Date data type, i came across statement "The ydm format is not supported for date."
I would like to know reason behind why YDM format is not supported?


#2

Only country that I could find that supports YDM is Kazakhstan and their preferred format is d.m.y ... maybe that's the reason? Either way, its not common ...

I recommend that you only use the unambiguous formats "YYYYMMDD" or "YYYY-MM-DD". IF you also use DateTime datatypes I recommend that (to avoid possible confisuion and bugs) you stick to "YYYYMMDD" because "YYYY-MM-DD" is not a recognised unambiguous format for conversion to DateTime.

If you use one of the other, ambiguous, formats then you are at the mercy of whatever Locale SQL decides to use for the conversion - and that is chosen based on things like the Language of the currently connected user - i.e. it might change like the wind!

If you have string data in a particular format and you want to "force" the conversion then one way to do it is with

SET DATEFORMAT dmy

immediately prior to making the date conversion.