Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
819 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
52326 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
52326 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  
 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.02 seconds. Powered By: Snitz Forums 2000