sql-server - Correct way to convert Month Name and Year to complete date SQL Server

Question

I have data in the format below.

January 2016
August 2017
November 2018
January 2018
August 2018
November 2018

I now want to convert this to a valid datetime value so i use the column as a proper date in reports.

My plan is to just add the first (or even better) the last day of the month. I have tried the below code, it seems to work but i'm wondering if there could be a better way.

DATEADD(dd, -Day(b.MonthYear) + 1, b.MonthYear)

With the above code, August 20117 gives me 2017-08-01 00:00:00.000 but maybe there could be a better way.

标签： sql-servertsqlsql-server-2014

Answer 1

The source of danger here his: You are relying on your system's culture.

Culture-dependant date formats are dangerous (What is 10/09/2017? Is it 9th of Oct or 10th of Sep?). But even worse are language dependant formats (October is Oktober in my country).

Try this

SET LANGUAGE ENGLISH;
SELECT TRY_CAST('January 2017' AS DATE),TRY_CONVERT(DATE,'January 2017')

SET LANGUAGE GERMAN;
SELECT TRY_CAST('January 2017' AS DATE),TRY_CONVERT(DATE,'January 2017')

The alternative calls without the TRY_ would not return NULL but throw an error.

But you are stating [sql-server-2014]. That means you can use TRY_PARSE() (v2012+). The big advantage: You can specify the underlying culture.

SET LANGUAGE GERMAN
SELECT TRY_PARSE('January 2017' AS DATE USING 'en-us')

If there is the smallest chance, that you code might ever run in international environments you should never ever rely on the default culture...

UPDATE performance vs. stability...

In a comment I answered @SQL_M:

I know what I'd choose... If dates are properly handled, you'd never have to deal with such a question. We should see textual formats in output only. The user's input should be handled by the application layer. So the database should never have to think about such conversions. The fact, that this need exists, shows clearly, that the database/application was not designed properly. We should not add more weakness if we can avoid it

This made me curious and I did some tests.

I must admit: The performance difference is undeniable and much bigger than expected. On a million rows with dates like Jan 1 1900 12:11AM (default with CONVERT, param 100) I get:

• TRY_CONVERT about 600ms
• TRY_CAST about 550ms
• TRY_PARSE about 45.000ms

So yes, it is really worth to be aware of the performance impact (~x100). But the statement above still remains true.

The test code - if interested:

USE master;
GO
CREATE DATABASE testDB
GO
USE testDB
GO
CREATE TABLE testTbl(ID INT IDENTITY,DateString VARCHAR(100));
GO
WITH Tally(Nmbr) AS (SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) FROM master..spt_values v1 CROSS JOIN master..spt_values v2 CROSS JOIN master..spt_values)
INSERT INTO testTbl(DateString)
SELECT CONVERT(DATETIME,DATEADD(MINUTE,t.Nmbr,'19000101'),100)
FROM Tally t;
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @d DATETIME2=SYSUTCDATETIME();
SELECT TRY_CONVERT(DATETIME,DateString) AS d INTO t1 FROM testTbl;
SELECT 'TRY_CONVERT', DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @d DATETIME2=SYSUTCDATETIME();
SELECT TRY_CONVERT(DATETIME,DateString,100) AS d INTO t2 FROM testTbl;
SELECT 'TRY_CONVERT with 3rd param', DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @d DATETIME2=SYSUTCDATETIME();
SELECT TRY_PARSE(DateString AS DATETIME) AS d INTO t3 FROM testTbl;
SELECT 'TRY PARSE',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @d DATETIME2=SYSUTCDATETIME();
SELECT TRY_PARSE(DateString AS DATETIME USING 'de-de') AS d INTO t4 FROM testTbl;
SELECT 'TRY PARSE with culture',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());
GO
CHECKPOINT;
GO
DBCC DROPCLEANBUFFERS
GO
DECLARE @d DATETIME2=SYSUTCDATETIME();
SELECT TRY_CAST(DateString AS DATETIME) AS d INTO t5 FROM testTbl;
SELECT 'TRY_CAST',DATEDIFF(MILLISECOND,@d,SYSUTCDATETIME());

GO
USE master;
GO
DROP DATABASE testDB;