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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case Statement

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_date

But 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

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.

Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -