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.

In any case, I made a joke on Twitter of asking if people were alright moving to YYYYMMDD. Most people were in favor, though someone asked about MMMM DD, YYYY, which is easily read. I have to say that I somewhat like that for reading, but as data, as something I’d work with, I prefer having the year month day format. I do want separators, and while I grew up using slashes ( as in YYYY/MM/DD), I know the ISO format is dashes (YYYY-MM-DD). ISO 8601 has dashes, so I decided to add that to the GitHub issue as a way of formatting.

What about using month names? As I look through various sites, I find inconsistencies. Some use dates, like Jan 11, 2019. Others might use 11 Jan 2019 or 2019-01-11. I find that MSDN uses regional settings, so the en-us version of a page will have 01/11/2019, but if I change to the en-Gb version, I see 11/01/2019. That seems find as a UI enhancement, but I hope that the actual value is stored as 2019-01-11. We don’t need them time

Or do we? There are times involved. While most of us might not care about whether something was published at 8am or 9am, we might care about where it was published and the time. Certainly 8am in Christchurch if far different from 8am in Honolulu. Do we store time zones? Or keep all dates in one? The logical thing is to keep dates in UTC and then adjust based on the client settings to display something that matches their time zone. We’ve been storing data in UTC, though the server time has been the Mountain time zone in the US for historical reasons. I don’t like that dependency, so I’m sure we’ll change that.

Dates and times are both simple and not simple. There are decisions to be made, and far too many of us take them lightly. While the display is of secondary importance, it’s likely that we’ll often use this data in other systems, as a way of marking a change, or even as a way of ordering our data. Having consistent and known date time formats is important as a data professional. I hope you pay attention to how you gather and store this data.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 4.3MB) podcast or subscribe to the feed at iTunes and Libsyn.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Editorial and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.