Trying to convert decimal to date using coalesce

1

I am new to SQL - fumbling and googling my way through some of the more complicated statements -
In this situation either of these dates may exist which is the reason for coalesce - syntactically I do not get an error with how this is coded but when I run the proc I get the trailing message -

CAST(cast(coalesce(nullif(l.eldldt,0), nullif(l.elupdt,0),0) as varchar(10)) as date)
returns the following message
Msg 241, Level 16, State 1, Line 34
Conversion failed when converting date and/or time from character string.

2

so, what's in eldldt when you get that message? also is that column a date/time column?

note, this will fail the same way:

select cast(cast(0 as varchar) as date)

since '0' is not a valid date string.

see https://msdn.microsoft.com/en-us/library/ms187928.aspx

3

Don't convert to varchar at all.

coalesce(nullif(l.eldldt,cast(0 as date)), nullif(l.elupdt,0),0)

If the columns are datetime, and you want to end up with a date, then add a final cast:

cast(coalesce(nullif(l.eldldt,cast(0 as date)), nullif(l.elupdt,0),0) as date)

4

Thanks, Scott!
I was actually able to get around it by using a CASE statement. I like what you provided so I'll go back and review and test this.

Did I exceed your expectations today?
Click herehttps://www.research.net/s/C8922H8?c=13010 to tell us about your experience.

5

I did go back and review the data and found that there were some existing records where there was no value in elupdt. So that was causing an issue and probably the message. Thanks again for the direction!