Date Coding Format - Is there any benefit of using YYYYMMDD OR DD-MM-YYYY? what is most common and why preffered?

• Jared

  SSC Guru

  Points: 61793

  More actions

  December 7, 2011 at 7:57 am

  #1418110

  MidBar (12/7/2011)

  ---

  Thanks Jeffrey,

  So ISO standard is the strongest reason of choosing this format i.e. "YYYYMMDD HHMMSS" which is widely adopted by most DBAs/Developers and easily understandable in most of DBMS including SQL server.

  This won't give you any added benefit in storage and retrieval but makes you consistent across all plate-forms.

  Thanks everyone.

  "YYYYMMDD HHMMSS" is not a valid format, it is "YYYYMMDD HH:MM:SS"... I'm not sure what you are getting at here, so run this:

  CREATE TABLE #dateTimeTest(dateField DATETIME, dateChar VARCHAR(20))

  INSERT INTO #dateTimeTest

  SELECT '2011-12-07', '2011-12-07'

  INSERT INTO #dateTimeTest

  SELECT '20111207', '20111207'

  INSERT INTO #dateTimeTest

  SELECT '12/07/2011', '12/07/2011'

  INSERT INTO #dateTimeTest

  SELECT '20111207 00:00:00', '20111207 00:00:00'

  INSERT INTO #dateTimeTest

  SELECT '20111207 000000', '20111207 000000' --YOU CANNOT DO THIS

  SELECT * FROM #dateTimeTest

  Keep in mind, the way that it stores it is not in any of these formats. That is just the way that you are coding it and it displays in the results as ISO. This is a scripting pattern, not a storage format. You could insert into a datetime field using 'YYYYMMDD' and I could insert into the same column using 'YYYY-MM-DD' or 'MM/DD/YYYY'. If the column you are inserting into is datetime, the engine itself realizes that you are inserting a date and interprets it as such as much as it can.

  Jared

  Jared
  CE - Microsoft