Which "date format" to use.....

• Barkingdog

  SSCoach

  Points: 18670

  More actions

  May 22, 2006 at 10:09 am

  #168209

  I want to setup a company-wide format for dates stored in our sql 2000 databases but I don't know the issues well enough to understand which format is preferred -- and for what reason.

  This

         mm/dd/yyyy  (?)

         yyyy/mm/dd  (?)

   

  or maybe this

   

         mm-dd-yyyy  (?)

         yyyy-mm-dd (?)

   

  or even this:

         yyyymmdd

   

  Any thoughts much appreciated.

   

  Bill

   

   

   

• Edwin Frydendall

  SSCrazy

  Points: 2361

  More actions

  May 22, 2006 at 10:14 am

  #639314

  Bill,

  I doesn't look like your company already has a standard for date format.

  Personally, I prefer yyyy-mm-dd, since the date parts are easily distinguishable, and it simplifies sorting.

   

• RyanRandall

  SSChampion

  Points: 13623

  More actions

  May 22, 2006 at 10:23 am

  #639320

  If you use the datetime data type (and you should!), the stored format is irrelevant. The format you use to query those fields is a different matter.

  This answers your question in detail:

  http://www.sql-server-performance.com/fk_datetime.asp

  Ryan Randall

  Solutions are easy. Understanding the problem, now, that's the hard part.

• SQLBill

  SSC Guru

  Points: 51440

  More actions

  May 22, 2006 at 10:57 am

  #639329

  My opinion:

  use the format of yyyy-mm-dd for inputting dates into the database. That format is always understood by SQL Server. dd-mm-yyyy or mm-dd-yyyy can be misunderstood by SQL Server unless you use the SET DATEFORMAT command. For example: what is 01/02/2006? Is that Jan 2, 2006 or 1 Feb, 2006? Can you read my mind? Neither can SQL Server. So, is 02/15/2006 - 15 Feb 2006 or the 2nd day of the 15 month (non-existent) of 2006? SQL Server will give an error for that (which is why SET DATEFORMAT exists).

  -SQLBill