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

Date Fun with SQL Server

I saw a people discussing date formats on Twitter after PASS put out an advertisement for a webinar with a mm-dd-yyyy format. Plenty of people were annoyed and wanted to be sure that they realized that much of the world might misinterpret 3-2-2018 as Feb 3, as opposed to the US Mar 2 view.

That’s fair, and while some people noted that yyyy-mm-dd is the best format, I saw this tweet from Mladen Prajdic. It looks at the DATEFORMAT setting can cause issues.

Here are a few reproductions. On my US centric system, I ran this:

SET LANGUAGE ‘us_english’
GO
SET DATEFORMAT YMD
GO
DECLARE @dt DATETIME = ‘2016-03-02’;
SELECT yyyy = YEAR(@dt) ,
mm = MONTH(@dt) ,
dd = DAY(@dt);
GO
SET LANGUAGE ‘French’;
DECLARE @dt DATETIME = ‘2016-03-02’;
SELECT yyyy = YEAR(@dt) ,
mm = MONTH(@dt) ,
dd = DAY(@dt);
GO
SET LANGUAGE ‘French’;
DECLARE @dt DATETIME = ‘20160302’;
SELECT yyyy = YEAR(@dt) ,
mm = MONTH(@dt) ,
dd = DAY(@dt);
GO
SET LANGUAGE ‘us_english’
GO
SET DATEFORMAT YMD

My results:

2018-03-27 10_56_20-SQLQuery10.sql - (local)_SQL2014.master (PLATO_Steve (53))_ - Microsoft SQL Serv

Now, let’s change a few things. I’ll move to datetime2. If I do this, all three queries return 2016, 3, 2 for year, month, day. The same thing occurs with the DATE datatype.

If you examine Mladen’s test, you’ll see that without dashes things work fine. However, with dashes, the datetime datatype has issues. These can manifest themselves with both SET LANGUAGE and SET DATEFORMAT settings.

One thing to keep in mind is that SET LANGUAGE will automatically change the date formats to match that language, and you can override those with SET DATEFORMAT if needed.

The takeaway? First, use modern datatypes. Not worth using datetime and avoiding the “2” if there is potential for incorrect dates.

Second, avoid the dashes for now.

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Comments

Leave a comment on the original post [voiceofthedba.com, opens in a new window]

Loading comments...