CODEWITHSUNDEEP
sqlsql-serverdatetimetry-cast
AleBel
AleBel

Reputation: 25

Incorrect Date Parsing

Could you help me determine if I am doing something wrong or if this is a SQL Server bug?

I am using a US SQL Server 2016 Enterprise Server. When I set the DATEFORMAT I do not get the expected results. Please see the comments in the code.

DECLARE @received varchar(50)

--I expect this to fail, but I get June 11, 2012.
SET DATEFORMAT 'ydm' 
SET @received = '11/JUN/12'

SELECT 
     [month]= DATENAME(month, TRY_CAST(@received as datetime ))
    ,[day]  = DATENAME(day, TRY_CAST(@received as datetime ))
    ,[year] = DATENAME(year, TRY_CAST(@received as datetime ))


--I expect this to be June 12, 2011, but I get June 11, 2012
SET DATEFORMAT 'ymd' 
SET @received = '11/JUN/12'

SELECT 
     [month]= DATENAME(month, TRY_CAST(@received as datetime ))
    ,[day]  = DATENAME(day, TRY_CAST(@received as datetime ))
    ,[year] = DATENAME(year, TRY_CAST(@received as datetime ))

Background info on why I try to validate the above instead of using 2011/06/12: We get data extracts in flat files from around the world, from a variety of Warehouse Management Systems and from sources with different degree of technical expertise. Sometimes our clients can produce the data and other times, they subcontract to get their data out from their systems. We deal with millions of rows at at time. So we try our best to validate the dates that they submit in the format they submit. Asking for a re-submit is very inconvenient and cost money and delays. Even though we ask for a preferred format, we do not always get it the first time.

Upvotes: 0

Views: 200

Answers (1)

Daniel Gimenez
Daniel Gimenez

Reputation: 20599

The issue you're having is that dateformat is limited to working with dates that have numeric values in them.

Changing JUN to 6 will cause the second example to work as you expected, while the first will return December 6th, 2011. The reason is because all the supported date formats for the format argument correspond to the 1 or 2 digit forms for day and month and the 2 and 4 digits forms for year with optional slash marks (/), hyphens (-), or periods (.) as separators. So for example for ymd corresponds to [yy]yy, [m]m, dd and that's it.

I don't know what mechanism in cast or try_cast causes the fallback behavior.

SET DATEFORMAT 'ymd' 
SET @received = '11/6/12'

SELECT 
     [month]= DATENAME(month, CAST(@received as datetime ))
    ,[day]  = DATENAME(day, CAST(@received as datetime ))
    ,[year] = DATENAME(year, CAST(@received as datetime ))

If you need the month as an abbreviation in the middle then it seems it is possible if you use parse and ja-JP culture. The following example will return the result you desired in your second example.

SET @received = '11/JUN/12'
SELECT 
     [month]= DATENAME(month, TRY_PARSE(@received AS date USING 'ja-JP'))
    ,[day]  = DATENAME(day, TRY_PARSE(@received AS date USING 'ja-JP'))
    ,[year] = DATENAME(year, TRY_PARSE(@received AS date USING 'ja-JP'))

Upvotes: 1

Related Questions