Best way to represent date value in Sql Server


I am going through Kalen Delaney and Itzik Ben-Gan’s Inside Sql Server 2005 books. I am surprised and happy to learn that you can always represents date/time value in the format of [YY]YYMMDD[ HH:MI[:SS][.MMM]] in Sql Server, with things inside the square bracket being optional. In fact, that is a recommended practice because you will always get intended results regardless of Windows locale settings, SET LANGUAGE, and SET DATEFORMAT options.

This representation is actually consistent with the convention used in China, so it feels pretty natural to me. In China, when people talk about dates, it is almost always written in the order of year, month, date, and goes more granular as needed such as hour, minutes, seconds, etc.

The same can be said about address. For example “100 Main Street, Oak Park, Illinois, USA” is the proper order of writing address in the US. The same address addressed in Chinese would be “USA, Illinois, Oak Park, 100 Main Street”

I wouldn’t be surprised if Japan, Korea, and other Asian countries use the same convention as China on this. I am not sure, though.

, ,

3 responses to “Best way to represent date value in Sql Server”

  1. I like to do (numeric, zero-padded) YYYYMMDDhhmm for, say, automatically-generated filenames on a Unix system, because they sort nicely that way. This can be extremely useful years later when you’re auditing a directory full of files and maybe want to do some ad-hoc stuff on a range of dates … it is much easier to say 20060[123]15 than say (January|February|March)15-2006 or something goofy like that.

    I believe the Europeans also are partial to YYYYMMDD or DDMMYYYY, and the whole mid-least-most significant madness is peculiar to America, which scoffs at useful international standards like the Metric system.

    -danny

  2. I do exactly the same thing for my automated Sql Server backup jobs. All backup files follow the convention of DbName_YYYYMMDDhhmm.bak format. Very useful indeed.

    You are right. Most Europeans like YYYYMMDD. I just consulted with my resident Sweden expert 🙂

    I prefer Metric system too. I guess US is probably the only country in the world that does not use Metric system. (Maybe the British converted to Metric system half-way.)

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.