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.

About way0utwest

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

2 Responses to Date Fun with SQL Server

  1. Yaroslav says:

    Hi Steve!
    Thanks for the post! It’s really very important to understand how date format behaves.
    I just would add that SET LANGUAGE implicitly sets the setting of SET DATEFORMAT. And if we write:
    SET LANGUAGE ‘French’;
    SET DATEFORMAT YMD;
    DECLARE @dt DATETIME = ‘2016-03-02’;
    It works fine.

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 )

w

Connecting to %s