Post reply

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

  • December 5, 2011 at 9:31 am

    #247208

    Hi,

    I wonder if One could tell me the benefit of using different date format in tsql or procedure coding.

    Does database engine works different or efficiently, if we use YYYYMMDD or DDMMYYYY or something similar?

    What is most common and why preffered?

    Thanks.

  • December 5, 2011 at 9:44 am

    #1416807

    MidBar (12/5/2011)

    Hi,

    I wonder if One could tell me the benefit of using different date format in tsql or procedure coding.

    Does database engine works different or efficiently, if we use YYYYMMDD or DDMMYYYY or something similar?

    What is most common and why preffered?

    Thanks.

    It really depends on how you are using it, but in a transactional database I prefer to use datetime so that I can use date functions and have accurate data. If you are setting up a datawarehouse and have dimensions of dates, integers or big integers may suit your purpose better since they are not used in date functions in SQL. I will never use the "date" data type in transactional data because it leaves off the time. It may be appropriate in a lookup table like a calendar table, but not for transactional rows.

    Jared

    Jared
    CE - Microsoft

  • December 5, 2011 at 9:47 am

    #1416809

    SQL Server stores datetime in YYYY-MM-DD hh:mm:ss[.fractional seconds] format. Rest all the formats are result of cast / convert functions and added operation on t-sql.

    CAST and CONVERT (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

  • December 5, 2011 at 10:19 am

    #1416850

    But if you are keeping personnel info & you have storage issues the 'date' format is the way to go IMHO.

    No one hardly ever puts the 'time' in on columns such as 'birth date' or 'start date'.

  • December 5, 2011 at 10:34 am

    #1416861

    Dev (12/5/2011)

    SQL Server stores datetime in YYYY-MM-DD hh:mm:ss[.fractional seconds] format. Rest all the formats are result of cast / convert functions and added operation on t-sql.

    CAST and CONVERT (Transact-SQL)

    http://msdn.microsoft.com/en-us/library/ms187928.aspx

    technically, that's not correct. datetime fields are stored as two integer values, and not as a decimal or any specia format. However, that format has the advantage of being the "standard", which all languages and DBMS systems should be able to translate to the correct datetime.

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/how-are-dates-stored-in-sql-server

    that's the reason datetime values are only accurate to 3 ms; the integer division prevents a tigheter scale.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • December 5, 2011 at 10:40 am

    #1416862

    that format has the advantage of being the "standard", which all languages and DBMS systems should be able to translate to the correct datetime.

    This is why I am in favor of storing things such as birthdate and hiredate as a datetime. You can always convert it to the DATE data type after the fact, but storing of the data in datetime will help with compatibility issues.

    Jared

    Jared
    CE - Microsoft

  • December 6, 2011 at 4:00 am

    #1417195

    Sorry if my question was not clear, as it was not about datatype date, time, datetime etc. However, question was about storage and retrieval of datetime values and which format gives benefit on other?

    As you just said SQLServer stores date in YYYY-MM-DD OR may be YYYYMMDD HHMMSS, so does this mean one who will store in this format will get extra advantage on others while retrieving the data?

    Thanks.

  • December 6, 2011 at 4:15 am

    #1417202

    No, datetime values are not stored in any format - just as two integers, like Lowell said. You can use varchar to store dates in any format you like, but it's not recommended since it takes more space, requires conversions and does not stop invalid dates getting into your database.

    John

  • December 6, 2011 at 6:03 am

    #1417286

    MidBar (12/6/2011)

    Sorry if my question was not clear, as it was not about datatype date, time, datetime etc. However, question was about storage and retrieval of datetime values and which format gives benefit on other?

    As you just said SQLServer stores date in YYYY-MM-DD OR may be YYYYMMDD HHMMSS, so does this mean one who will store in this format will get extra advantage on others while retrieving the data?

    Thanks.

    I think you are asking if there is a difference between inserting '2011-12-06', '20111206', or '2011-12-06 00:00:00.000' into a datetime column. If that is your question, the answer is no. It is all stored the same way. The engine interprets the insert for each one of these and stores the exact same value for each one of these.

    Thanks,

    Jared

    Jared
    CE - Microsoft

  • December 6, 2011 at 9:40 am

    #1417480

    OK will we get any advantage in query performance if we write condition in select like this 2011-09-23 11:12:13

    OR

    can be any dis-advantage if we write like 23/sep/2011 111213?

    "YYYY-MM-DD HH:MM:SS " Standard for most DBMS is good point but any other additional advantage more specifically in SELECT will give extra benefit?

    Thanks to all of You.

  • December 6, 2011 at 9:49 am

    #1417489

    MidBar (12/6/2011)

    OK will we get any advantage in query performance if we write condition in select like this 2011-09-23 11:12:13

    OR

    can be any dis-advantage if we write like 23/sep/2011 111213?

    "YYYY-MM-DD HH:MM:SS " Standard for most DBMS is good point but any other additional advantage more specifically in SELECT will give extra benefit?

    Thanks to all of You.

    No, it is the exact same thing.

    Jared

    Jared
    CE - Microsoft

  • December 6, 2011 at 12:30 pm

    #1417619

    MidBar (12/6/2011)

    OK will we get any advantage in query performance if we write condition in select like this 2011-09-23 11:12:13

    OR

    can be any dis-advantage if we write like 23/sep/2011 111213?

    "YYYY-MM-DD HH:MM:SS " Standard for most DBMS is good point but any other additional advantage more specifically in SELECT will give extra benefit?

    Thanks to all of You.

    Using the format '23/sep/2011 111213' in a where clause will cause an error. That is not a supported datetime format - as far as I know. It might work - if your regional settings allow for it, which is also part of the problem with this format. It would be dependent on the regional settings and could be misinterpreted.

    Using 'YYYY-MM-DD HH:MM:SS' is an ISO standard, but is not necessarily recognized in SQL Server as an unambiguous format. This can also be misinterpreted based on the dateformat (regional settings). In other words, if your date format is YDM then this will be interpreted incorrectly and could cause problems.

    Using either:

    'YYYYMMDD HH:MM:SS'

    'YYYY-MM-DDTHH:MM:SS'

    Will be ISO compliant and unambigous - which means they will be interpreted correctly all the time.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • December 6, 2011 at 12:41 pm

    #1417623

    Jeffrey Williams 3188 (12/6/2011)

    MidBar (12/6/2011)

    OK will we get any advantage in query performance if we write condition in select like this 2011-09-23 11:12:13

    OR

    can be any dis-advantage if we write like 23/sep/2011 111213?

    "YYYY-MM-DD HH:MM:SS " Standard for most DBMS is good point but any other additional advantage more specifically in SELECT will give extra benefit?

    Thanks to all of You.

    Using the format '23/sep/2011 111213' in a where clause will cause an error. That is not a supported datetime format - as far as I know. It might work - if your regional settings allow for it, which is also part of the problem with this format. It would be dependent on the regional settings and could be misinterpreted.

    Using 'YYYY-MM-DD HH:MM:SS' is an ISO standard, but is not necessarily recognized in SQL Server as an unambiguous format. This can also be misinterpreted based on the dateformat (regional settings). In other words, if your date format is YDM then this will be interpreted incorrectly and could cause problems.

    Using either:

    'YYYYMMDD HH:MM:SS'

    'YYYY-MM-DDTHH:MM:SS'

    Will be ISO compliant and unambigous - which means they will be interpreted correctly all the time.

    I think the important thing to understand is that SQL Server does not store datetime as '2011-10-12' OR '2011/10/12' or anything with slashes, hyphens, or colons. It is interpreted by the engine and stored as described earlier. The point is, changing the format of how you are requesting or inputting the date does not change performance or storage as long as the data type is datetime.

    Jared

    Jared
    CE - Microsoft

  • December 6, 2011 at 1:26 pm

    #1417647

    I'm not sure if this helps the OP, besides the storage aspect of date types, the other thing to consider when using date literals is that there are ANSI, ISO, SO8601 and non-standard formats. So, if you are going to use a literal you should use one that cannot be confused with a different date based on reginal settings (i.e. ISO8601).

  • December 7, 2011 at 6:44 am

    #1418045

    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.

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply