Dealing with out of range value on varchar date conversion

Question

I'm attempting to convert dates input in our system as text in the format YYYYMMDD into dates. Unfortunately our system allows the use of the 31st of any month to signify that it's the last day of the month that's important, for some functions like interest accrual etc.

I have a date showing as 20160931 which obviously fails to convert via

CONVERT(DATETIME, CONVERT(CHAR(8), [FIELD]))

and throws the out-of-range value error.

How can I overcome this, so that I can convert it to the correct value, in this case 30/09/2016?

Answer 1

You might try something like this:

DECLARE @YourDate VARCHAR(100)='20160231';

SELECT CAST(y+m+dNew AS DATE)
FROM (VALUES(LEFT(@YourDate,4)
           ,SUBSTRING(@YourDate,5,2)
           ,SUBSTRING(@YourDate,7,2))) AS Parts(y,m,d)
CROSS APPLY
(
    SELECT CASE WHEN CAST(m AS INT) IN(4,6,9,11) AND CAST(d AS INT)>30 THEN '30' 
           ELSE CASE WHEN CAST(m AS INT)=2 AND CAST(d AS INT)>28 THEN '28' ELSE d END 
           END AS dNew
) AS NewDay

And about a 29th of February you just have - additionally - to check if the year is to be divided by 4 :-)

UPDATE

Now it's on me to evolve @Irawan's technique :-)

Since SQL Server 2005 has not got the EOMONTH function, but it is surely better to let SQL Server do the calculation (29th of Feb implicitly solved!), I'd suggest this:

DECLARE @YourDate VARCHAR(100)='20160231';

SELECT DATEADD(SECOND,-1,DATEADD(MONTH,1,CAST(y+m+'01' AS DATETIME)))
FROM (VALUES(LEFT(@YourDate,4)
           ,SUBSTRING(@YourDate,5,2)
           ,SUBSTRING(@YourDate,7,2))) AS Parts(y,m,d)

This will - in any case - deliver the last second of the month...

If you want a plain DATE (without a time), you might just change the SECOND to DAY which will first jump to midnight of the first day of the next month and than go one day back...

UPDATE 2 Use existing dates if valid

Simple syntax...

DECLARE @YourDate VARCHAR(100)='20160229';

SELECT CASE WHEN ISDATE(@YourDate)=1 THEN @YourDate 
            ELSE DATEADD(SECOND,-1,DATEADD(MONTH,1,CAST(LEFT(@YourDate,4) + SUBSTRING(@YourDate,5,2) +'01' AS DATETIME)))
            END AS CorrectDate;