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:
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.
