Explicit conversion of bigint to date

Question

I'm fixing an existing code but got stuck with it because SQL server keeps showing an error related to converting string to date and then bigint to date. so not sure which technique to use:

below is my code:

SELECT
c.WCFromDate
,c.WCToDate
,CONVERT(date, CAST(
                    CASE
                        WHEN c.WCFromDate = 0  THEN 19000101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) = '00' AND RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) = '02' AND RIGHT(c.WCFromDate,2) > '29'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0228
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) < '13' AND RIGHT(c.WCFromDate,2) > '31'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCFromDate as varchar(10)), 5, 2) > '12'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        WHEN RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        ELSE c.WCFromDate
                    END AS varchar(10))
        ) AS [Worker Compensation From Date]
,CONVERT(date, CAST(
                    CASE
                        WHEN c.WCToDate = 0 THEN 19000101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) = '00' AND RIGHT(c.WCToDate,2) = '00'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) = '02' AND RIGHT(c.WCToDate,2) > '29'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0228
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) < '13' AND RIGHT(c.WCToDate,2) > '31'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0101
                        WHEN SUBSTRING(CAST(c.WCToDate as varchar(10)), 5, 2) > '12'
                            THEN LEFT(c.WCToDate, 4) * 10000 + 0101
                        WHEN RIGHT(c.WCFromDate,2) = '00'
                            THEN LEFT(c.WCFromDate, 4) * 10000 + 0101
                        ELSE c.WCToDate
                    END AS varchar(10))
        ) AS [Worker Compensation To Date]
        FROM  ls3dwh.Claims c

To be more specific, all case when statements are working fine but the ELSE part is causing this error. I used ELSE NULL to find out more and noticed that all ELSE values are causing this error.

The column values are pretty simple, below screenshot can help:

all 4 columns

And here is some sample data from the first two columns. I used a select distinct:

   WCFromDate   WCToDate
   0        0
   19456601 20030000
   19580800 20300500
   19670000 0
   19840529 19840603
   19850314 19851115
   19851121 19910110
   19860217 19861112

The error I get is this:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

Answer 1

Your problem stems from the fact that you have different data types being returned, so the compiler attempts to coerce them all to the higher-precedence type, which is in this case date.

Instead you can just always return DATEFROMPARTS, or a conversion if necessary.

Assuming your data types really are varchar then you can clean up the code and simplify the various conditions like this:

SELECT
  c.WCFromDate
 ,c.WCToDate
 ,CASE
    WHEN c.WCFromDate = '0'
      THEN '19000101'
    WHEN SUBSTRING(c.WCFromDate, 5, 2) = '02' AND RIGHT(c.WCFromDate, 2) > '29'
      THEN DATEFROMPARTS(LEFT(c.WCFromDate, 4), 2, 28)
    WHEN SUBSTRING(CAST(c.WCFromDate, 5, 2) NOT BETWEEN '01' AND '13'
         OR RIGHT(c.WCFromDate, 2) NOT BETWEEN '01' AND '31'
      THEN DATEFROMPARTS(LEFT(c.WCFromDate, 4), 1, 1)
    ELSE
      TRY_CONVERT(date, c.WCFromDate, 112)
    END AS [Worker Compensation From Date]
 ,CASE
    WHEN c.WCToDate= '0'
      THEN '19000101'
    WHEN SUBSTRING(c.WCToDate, 5, 2) = '02' AND RIGHT(c.WCToDate, 2) > '29'
      THEN DATEFROMPARTS(LEFT(c.WCToDate, 4), 2, 28)
    WHEN SUBSTRING(CAST(c.WCToDate, 5, 2) NOT BETWEEN '01' AND '13'
         OR RIGHT(c.WCToDate, 2) NOT BETWEEN '01' AND '31'
      THEN DATEFROMPARTS(LEFT(c.WCToDate, 4), 1, 1)
    ELSE
      TRY_CONVERT(date, c.WCToDate, 112)
    END AS [Worker Compensation To Date]
FROM  ls3dwh.Claims c

If your data type is actually bigint then I suggest you use % and / division to get the different parts, rather than SUBSTRING.

Answer 2

Have you considered using IsDate(MyColumnValue) to check for valid dates, rather than parsing out over several case statements to check for date validity? That may make the process a little cleaner and easier to understand and debug, and may help you get the output you need. I just quick-checked and it works "as is" with values formatted like those you've shared.

Answer 3

Try the following case expression:

Select case 
        when len(d)=8 and ((d%10000)=0 or (d%10000) >1231 or (d%100)>31 
        or (d%100)=0  or (d%10000/100)=0
        or ((d%10000/100) in(4,6,9,11) and d%100>30))
        then cast(cast(d+101-(d%10000) as varchar(10)) as date)
        
        when len(d)=8 and ((d%10000) between 230 and 299)
        then cast(cast(d+228-(d%10000) as varchar(10)) as date)
        
        when d=0 then cast('19000101' as date)
        else cast(d as date)
      end as dates
from dts

The expression will check for all possible errors (as you listed) in the date values and fix it according to your criteria.

See a demo from here.