CODEWITHSUNDEEP
sql
James Parish
James Parish

Reputation: 337

SQL Error : Conversion failed when converting datetime from character string

I've got the following piece of code which exectues within no problem

SELECT (
   CASE WHEN 
       (SELECT DateDiff (Day, (
        SELECT ChildDOB1 FROM MatterDataDef Where ptMatter = $Matter$), 
        GETDATE()))>6574 THEN '(over 18)' 
   ELSE '(' + ChildDOB1 + ')' 
   END)  
FROM dbo.MatterDataDef WHERE ptMatter = $Matter$

Howeveer, when i attempt to wrap the code in brackets (so i can use it as part of a longer equation) i get the following error 'Conversion failed when converting datetime from character string'

any help appreciated :)

Upvotes: 0

Views: 1585

Answers (2)

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

SELECT CASE
           WHEN Datediff (DAY, childdob1, Getdate()) > 6574
           THEN '(over 18)'
           ELSE '(' + convert(varchar,childdob1) + ')'
         END
FROM   dbo.matterdatadef
WHERE  ptmatter = $matter$

This is equivalent to your code. The extra subquery and brackets are all unnecessary. The problem is that you are adding childdob1 (datetime) to the brackets without converting to varchar. But did you actually want the DOB in a particular format, or the age?

Upvotes: 1

Jason Jong
Jason Jong

Reputation: 4330

where you have 

ELSE '(' + ChildDOB1 + ')'

you'll need to convert it to a character type.. ie varchar

ELSE '(' + cast(ChildDOB1 as varchar(50)) + ')'

Upvotes: 1

Related Questions