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:
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.
CodePudding user response:
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
.
CodePudding user response:
Have you considered using IsDate(MyFieldValue) 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.
CodePudding user response:
Try the following case expression:
Select case
when len(d)=8 and ((d000)=0 or (d000) >1231 or (d0)>31
or (d0)=0 or (d000/100)=0
or ((d000/100) in(4,6,9,11) and d0>30))
then cast(cast(d 101-(d000) as varchar(10)) as date)
when len(d)=8 and ((d000) between 230 and 299)
then cast(cast(d 228-(d000) 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.