Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
anishap
Yak Posting Veteran
61 Posts |
Posted - 2013-03-20 : 15:13:36
|
Hello,I want to find out employees who had training based on their job code otherwise it should say not applicable. So here is the case statement I tried,CASE WHEN JOBCODE NOT IN ('0112','0345','0442') THEN 'N/A' ELSE TRDATE END) AS Training_dateBut I'm getting the below error"Conversion failed when converting datetime from character string"I'm not an expert in T-SQL. Any help on this is appreciated.many thanks for your time |
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2013-03-20 : 15:51:48
|
Your case expression is returning a string - but you have a datetime data type being returned. The expression needs to convert that datetime value to a string and is failing.I would just return a NULL value for those that don't apply, instead of using N/A.CASE WHEN JOBCODE IN ('0112', '0345', '0442') THEN TRDATE END) AS Training_Date |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-21 : 02:05:12
|
or add a default date values like 19000101 or 17530101 depending on your business rules------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
anishap
Yak Posting Veteran
61 Posts |
Posted - 2013-03-21 : 14:33:49
|
Thanks for the response.I was able to fix the problem, see the updated case statement. CASE WHEN JOBCODE NOT IN ('0112','0345','0442') THEN 'N/A' ELSE CONVERT(VARCHAR(10),F.FLDDATE,101) END) AS TRDATE. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-22 : 01:59:23
|
i dont recommend this as making date values varchar will make it increasing difficult for you to do any further manipulations with them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|