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

Fun with Dates and Times

By Steve Jones,

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.

 
Total article views: 80 | Views in the last 30 days: 19
 
Related Articles
FORUM

date format in stored procedure

date format in stored procedure

FORUM

Which Stored Procedure changed

Which Stored Procedure changed

FORUM

How to Change the default date format in sqlserver 2005

change the default Date format..

FORUM

To Store Formatted Data or Not

Should data such as phone number and postal code be stored formatted?

FORUM

Storing DTS packages into .vbs format

Storing DTS packages into .vbs format

Tags
editorial    
 
Contribute