January 15, 2019 at 9:59 pm
Comments posted to this topic are about the item Fun with Dates and Times
January 15, 2019 at 11:43 pm
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.
January 15, 2019 at 11:56 pm
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.
January 16, 2019 at 1:32 am
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.
January 16, 2019 at 4:35 am
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.
January 16, 2019 at 4:48 am
Prefer YYYYMMDD to be honest.
January 16, 2019 at 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 with the day for certain languages.
January 16, 2019 at 4:54 am
Jonathan AC Roberts - Wednesday, January 16, 2019 4:50 AMI 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.
January 16, 2019 at 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..
January 16, 2019 at 6:14 am
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!
January 16, 2019 at 6:17 am
Rick-153145 - Wednesday, January 16, 2019 5:03 AMI 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.
January 16, 2019 at 6:24 am
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
January 16, 2019 at 6:36 am
Jerry Kelk - Wednesday, January 16, 2019 6:17 AMThey 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
Change is inevitable... Change for the better is not.
January 16, 2019 at 6:51 am
Jeff Moden - Wednesday, January 16, 2019 6:36 AMActually, 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.
January 16, 2019 at 7:00 am
Steve Jones - SSC Editor - Tuesday, January 15, 2019 9:59 PMComments 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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply