Fun with Dates and Times

Question

Fun with Dates and Times

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
January 15, 2019 at 9:59 pm

#370246

Comments posted to this topic are about the item Fun with Dates and Times

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

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

sean redmond SSCertifiable Points: 5824 More actions · Answer 1

I'm not entirely consistent on writing dates. I try to use YYYY-MM-DD whenever I can but I do accept that it is not what people outside East Asia expect.
Whenever I have something that will be sorted, say files or photos, I use YYYY-MM-DD.
However, if I'm writing a letter (remember those!), I will as often use MMM DD, YYYY as I will YYYY-MM-DD. I find the former nicer to read. I still need to think about about whether September is month 9 or not.

neels 2599 SSC Rookie Points: 33 More actions · Answer 2

You will have no problem if you format the Date as dd-MMM-yyyy or MMM dd yyyy ...or what so ever.
Note that the Month is always formatted as Jan/Feb/Mar etc . with a 2 digit day and a 4 digit year.
It will always be correctly interpreted by all systems.

Russell Lavelle-langham SSC Veteran Points: 261 More actions · Answer 3

I'm with Grasshopper - removing ambiguity with MMM is the way to go . . although I have to confess to using <context>_YYYYMMDD_HHmmSS for time-series files where I control the location and thus remove ambiguity by local audience agreement.

Jerry Kelk SSC Enthusiast Points: 127 More actions · Answer 4

I have long felt that the ambiguity between MMDDYYYY and DDMMYYYY, with or without separators, is intolerable and should have been terminated on Day 2 of SQL's arrival in the world - and not just in SQL.
Then I start to wonder what I would prefer; what I would prefer to see. It's not so easy, is it?
In reality, North America (or wherever) should probably be able to have its weird format, though I think it's ill-advised. America probably sees UK/Europe's format in a similar light. And yet, despite the electronic age, dates still get printed on paper or committed to JPG, PDF or other non-changing format, suggesting that this difference should indeed be banished because of its inherent uncertainty or even its capacity for mistakes.
DD MMM, YYYY or MMM DD, YYYY works well visually as a local format but, again, once committed to a non-changing medium could even be in the wrong/unexpected language, making the month hard to understand in other parts of the world and therefore open to mistakes.
Further, if you copy the MMM-style text from a table in PDF or perform OCR on a JPG version, you get dates which are now not sortable (as text), cannot be translated reliably and you may not know the source language of that 'hard copy' anyway. Of course, if all the dates listed are in the first 12 days of the month, in a printed numeric date format, the DDMM.. or MMDD.. format remains unknown, which is completely unacceptable.
So I think YYYY-MM-DD should be the universal favourite because it bypasses all of these problems. It is not the prettiest to read and you may feel the need to do a month translation in your head but it does mean the same thing in every country and in every format (soft or hard) and of course it sorts well as text, and can easily be converted to a date or datetime datatype with no chance of error.

The reality of our job is that we essentially spend our working life translating data from one form to another, one system to another, slicing and dicing it for consumption by various people in various ways in various mediums. Having ANY chance of uncertainty about something as fundamental as a date is surely unacceptable.

The world should probably just get used to YYYY-MM-DD format because that world is now a much smaller place and data in all its forms crosses borders, constantly. And it is supposedly an International Standard - ISO8601.

Jerry.

paul s-306273 SSChampion Points: 10730 More actions · Answer 5

paul s-306273

SSChampion

Points: 10730

January 16, 2019 at 4:48 am

#2019049

Prefer YYYYMMDD to be honest.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

I find YYYY-MM-DD a bit more readable than YYYYMMDD.
I once switched my SQL queries to YYYYMMDD from YYYY-MM-DD as there was a problem with the ISO 8601 dashed standard in that SQL Server's datetime (not datetime2) will swap the month with the day for certain languages.

Jerry Kelk SSC Enthusiast Points: 127 More actions · Answer 7

Jonathan AC Roberts - Wednesday, January 16, 2019 4:50 AM
I find YYYY-MM-DD a bit more readable than YYYYMMDD.
I once switched my SQL queries to YYYYMMDD from YYYY-MM-DD as there was a problem with the ISO 8601 dashed standard in that SQL Server's datetime (not datetime2) will swap the month day for certain languages.

Yuck!
Yes, separators are good, and probably dashes are best.
I feel it's the order of the elements that really should be adopted. Unambiguous - the only way.

Rick-153145 SSCrazy Points: 2823 More actions · Answer 8

I always store them as YYYYMMDD HH:MM:SS.MS (Or without time if Date), I should use UTC and have at a couple of places, but I think it is business dependent, although maybe it shouldn't be in this day and age.

Either way, the way you want to display the date is then entirely up to you.

It does amuse me though that Americans are the only ones to use the weird MMDDYYYY format, I say kill that with fire..

ron.bodhorn Newbie Points: 9 More actions · Answer 9

All of this assumes that you are using a normal calendar. We also have to deal with Julian, and a 4-5-4 fiscal business calendar to make our fun even more funner!

Jerry Kelk SSC Enthusiast Points: 127 More actions · Answer 10

Rick-153145 - Wednesday, January 16, 2019 5:03 AM
I always store them as YYYYMMDD HH:MM:SS.MS (Or without time if Date), I should use UTC and have at a couple of places, but I think it is business dependent, although maybe it shouldn't be in this day and age.
Either way, the way you want to display the date is then entirely up to you.
It does amuse me though that Americans are the only ones to use the weird MMDDYYYY format, I say kill that with fire..

They are always stored as a long decimal. It's about how you choose to display them by default or for a particular job.
My point was, unless the target audience is definitely only local, any committed display (jpg, pdf, print,...) should be in YYYY-MM-DD or it can be misinterpreted, which must be bad.
mmddyyyy is just crazy IMHUKO.

Jerry Kelk SSC Enthusiast Points: 127 More actions · Answer 11

Ask yourself:
How many times have I looked at dates on web pages and found myself looking for more examples to see whether the first bit or the middle bit is greater than 12 or whether the domain ends in .com or .co.uk (etc) ?
If the answer is as much as 'once', the problem needs fixing.
If the answer is 'never' then you've probably made wrong assumptions. Ok, it may not matter on many occasions but it's a bad habit which can come to bite you.

Jerry

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 12

Jerry Kelk - Wednesday, January 16, 2019 6:17 AM
They are always stored as a long decimal. It's about how you choose to display them by default or for a particular job.
My point was, unless the target audience is definitely only local, any committed display (jpg, pdf, print,...) should be in YYYY-MM-DD or it can be misinterpreted, which must be bad.
mmddyyyy is just crazy IMHUKO.

Actually, DATETIME is stored as two integers, the first being the number of days since the first of January, 1900 (day "0") and the second being the number of 1/300ths of a second since midnight (which is why the resolution of the datatype is 3.3ms). The cool part about DATETIME (and SMALLDATETIME) is that they're built to allow direct date math where the newer types are not.

I DO absolutely agree with you that the only time a temporal datatype should be formatted is for display purposes.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sean redmond SSCertifiable Points: 5824 More actions · Answer 13

Jeff Moden - Wednesday, January 16, 2019 6:36 AM
Actually, DATETIME is stored as two integers, the first being the number of days since the first of January, 1900 (day "0") and the second being the number of 1/300ths of a second since midnight (which is why the resolution of the datatype is 3.3ms). The cool part about DATETIME (and SMALLDATETIME) is that they're built to allow direct date math where the newer types are not.

This is SMALLDATETIME surely? DATETIME allows for dates that go all the way back to 1753.

Eric M Russell SSC Guru Points: 125561 More actions · Answer 14

Steve Jones - SSC Editor - Tuesday, January 15, 2019 9:59 PM
Comments posted to this topic are about the item Fun with Dates and Times

... The other day I got a Github issue that asked if we here at SQLServerCentral should set our dates as YYYYMMDD in the new SQLServerCentral site. The issue noted that there were some inconsistent dates. Article dates are in a similar format, YYYY/MM/DD, but we do have some DD MMM YYYY and a few places where I think the American MMBBYYYY has lived on. ...

Steve, I'm a little confused; are you talking about storing date/time strings in VARCHAR columns or just how the date/time is displayed at various points in the site?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho