CODEWITHSUNDEEP
sql-servert-sqldatesql-server-2012case
Skywise
Skywise

Reputation: 429

Cannot construct data type date, some of the arguments have values which are not valid

I have a date in my database which should not have included the year, so I need to construct the correct date by checking the date. (If the date is yet to come this year, I want to set 2017, if the date has passed, I want to set 2018) (t.myDate is a nullable datetime)

SELECT
    NewDate = CASE
                 WHEN DATEPART(dayofyear, GETDATE()) < DATEPART(dayofyear, t.myDate)
                    THEN DATEFROMPARTS('2017', DATEPART(MONTH,t.myDate), DATEPART(DAY, t.myDate))
                    ELSE DATEFROMPARTS('2018', DATEPART(MONTH,t.myDate), DATEPART(DAY,t.myDate))
              END
FROM
    MyTable t

However, in some cases I get the following error message:

Cannot construct data type date, some of the arguments have values which are not valid.

I'm not sure what the problem is, but I know some rows are NULL. So I have tried to get around the problem by using the CASE expression to simply use today's date in the cases where the problem is:

SELECT 
    NewDate = CASE
                 WHEN t.myDate is null
                    THEN GETDATE()
                 WHEN DATEPART(MONTH,t.myDate) < 1
                    THEN GETDATE()
                 WHEN DATEPART(MONTH,t.myDate) > 12
                    THEN GETDATE()
                 WHEN DATEPART(DAY,t.myDate) < 1
                    THEN GETDATE()
                 WHEN DATEPART(DAY,t.myDate) > 31
                    THEN GETDATE()
                 WHEN ISDATE(t.myDate) = 0
                    THEN GETDATE()
                 WHEN DATEPART(dayofyear, GETDATE()) < DATEPART(dayofyear, t.myDate)
                    THEN DATEFROMPARTS('2017', DATEPART(MONTH,t.myDate), DATEPART(DAY, t.myDate))
                ELSE DATEFROMPARTS('2018', DATEPART(MONTH,t.myDate), DATEPART(DAY,t.myDate))
             END
FROM
    MyTable t

But I still get the same error. Either I'm testing for the wrong data quality issues, or the CASE expression is not working as I expect it to.

Also: It does not help to use a where clause for NULL rows:

WHERE t.myDate IS NOT NULL

How can I find the data quality issue, and how can I get around it?

(I'm using SQL Server 2012)

Upvotes: 1

Views: 4995

Answers (1)

nejcs
nejcs

Reputation: 1262

Apart from checking for NULL you may also have a problem with 29th of February. If you for example have date 2016-02-29 and you are trying to construct date 2017-02-29 you will get above error since this is not a valid date.

In general you could use cursor to loop over records and execute your logic inside TRY/CATCH. On exception you could print offending data and check what the problem is.

Upvotes: 4

Related Questions