October 15, 2009 at 9:14 am
Hi ,
I have a case statement where i am checking for some dates and displaying a number for each date.
When the date is null i need to display #N/A.
CASE
WHEN Convert(varchar(13),column1,112) < date1 THEN 1
WHEN Convert(varchar(13),column1,112) < date2 THEN 2
WHEN Convert(varchar(13),column1,112) < date3 THEN 3
WHEN Convert(varchar(13),column1,112) IS NULL THEN '#N/A' ELSE 4
END AS Grp
The Column1 is actually a datetime.
error: Conversion failed when converting the varchar value '#N/A' to data type int.
Please suggest..
Thanks,
Nemo
October 15, 2009 at 9:20 am
I guess the values in the THEN clause should all be of same data type!
Here, first three case you are assigning integer values (1,2 and 3) and the last one is a varchar.
---------------------------------------------------------------------------------
October 15, 2009 at 12:31 pm
CASE
WHEN Convert(varchar(13),column1,112) < date1 THEN '1'
WHEN Convert(varchar(13),column1,112) < date2 THEN '2'
WHEN Convert(varchar(13),column1,112) < date3 THEN '3'
WHEN Convert(varchar(13),column1,112) IS NULL THEN '#N/A' ELSE '4'
END AS Grp
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
October 15, 2009 at 3:08 pm
May I ask, where are date1, date2, date3 values taken from?
Regards
Piotr
...and your only reply is slàinte mhath
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply