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
 General SQL Server Forums
 New to SQL Server Programming
 Problem Converting date format

Author  Topic 

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-11-14 : 12:45:20
In my SELECT statement, I am converting the various dates based on a code in another column like this

FEP_DATE =
case EnCode
when '3' then CONVERT(nvarchar(10), LepDate,112)
when '4' then CONVERT(nvarchar(10), recdate,112)
when '2' then CONVERT(nvarchar(10), SEntry,112)
when '1' then convert(datetime, NULL)
end

LepDate is datetime format 2002-01-17 00:00:00 & I want to convert it to yyyymmdd format. It does not in above example. But CONVERT functions works fine if tested by itself on this date. What am I doing wrong in my CASE statement?

Niki

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-14 : 13:01:09
change this:
when '1' then convert(nvarchar(10), NULL)
all cases should return the same datatype


Be One with the Optimizer
TG
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 13:01:36
Try changing this
when '1' then convert(datetime, NULL)
to
when '1' then convert(nvarchar(10), NULL)
The Case statement must reurns values of the same datatype


Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-11-14 : 13:03:35
quote:
Originally posted by TG

change this:
when '1' then convert(nvarchar(10), NULL)
all cases should return the same datatype


Be One with the Optimizer
TG



Wow! It's been a while since i was nsiped like that!

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2012-11-14 : 13:05:33


Be One with the Optimizer
TG
Go to Top of Page

Niki
Yak Posting Veteran

51 Posts

Posted - 2012-11-14 : 13:07:02
Wow! Thanks a bunch! it worked!

Niki
Go to Top of Page
   

- Advertisement -