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

Question

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

iBar

SSCertifiable

Points: 5872
More actions
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.

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

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

Jared SSC Guru Points: 61793 More actions · Answer 1

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

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

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

Sqlraider SSCertifiable Points: 5441 More actions · Answer 3

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'.

Lowell SSC Guru Points: 323518 More actions · Answer 4

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!

Jared SSC Guru Points: 61793 More actions · Answer 5

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

iBar SSCertifiable Points: 5872 More actions · Answer 6

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.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 7

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

Jared SSC Guru Points: 61793 More actions · Answer 8

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

iBar SSCertifiable Points: 5872 More actions · Answer 9

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.

Jared SSC Guru Points: 61793 More actions · Answer 10

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

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

Jared SSC Guru Points: 61793 More actions · Answer 12

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

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 13

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).

iBar SSCertifiable Points: 5872 More actions · Answer 14

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.