SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Fun with Dates and Times


Fun with Dates and Times

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)SSC Guru (651K reputation)

Group: Administrators
Points: 651201 Visits: 21472
Comments posted to this topic are about the item Fun with Dates and Times

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Sean Redmond
Sean Redmond
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5100 Visits: 1090
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
neels 2599
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 6
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
Russell Lavelle-langham
SSC Veteran
SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)SSC Veteran (256 reputation)

Group: General Forum Members
Points: 256 Visits: 157
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
Jerry Kelk
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 61
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
paul s-306273
SSCrazy Eights
SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)SSCrazy Eights (9.6K reputation)

Group: General Forum Members
Points: 9581 Visits: 1572
Prefer YYYYMMDD to be honest.
Jonathan AC Roberts
Jonathan AC Roberts
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13804 Visits: 5772
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
Jerry Kelk
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 61
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
Rick-153145
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2456 Visits: 775
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
ron.bodhorn
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search