SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Case Statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

anishap
Yak Posting Veteran

61 Posts

Posted - 03/20/2013 :  15:13:36  Show Profile  Reply with Quote
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

USA
806 Posts

Posted - 03/20/2013 :  15:51:48  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/21/2013 :  02:05:12  Show Profile  Reply with Quote
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 - 03/21/2013 :  14:33:49  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 03/22/2013 :  01:59:23  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000