SQL Error : Conversion failed when converting datetime from character string

Question

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 :)

@Jason Jong - which last line? do you mean the error text? The error could also occur if childdob1 itself is not datetime, because datediff is happening first — RichardTheKiwi
– RichardTheKiwi, Commented Jan 13, 2011 at 11:32

RichardTheKiwi · Accepted Answer · 2011-01-13 11:18:02Z

1

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?

answered Jan 13, 2011 at 11:18

RichardTheKiwi

108k28 gold badges206 silver badges269 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

James Parish Over a year ago

thanks, thts got it working (plus thanks again for striping down my code for me, i'm new to sql and nice to learn new things)... dateformat i've got convert(varchar(10),childdob1, 103) ;)

Jason Jong · Accepted Answer · 2011-01-13 11:16:44Z

1

where you have

ELSE '(' + ChildDOB1 + ')'

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

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

answered Jan 13, 2011 at 11:16

Jason Jong

4,3302 gold badges28 silver badges33 bronze badges

Collectives™ on Stack Overflow

SQL Error : Conversion failed when converting datetime from character string

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related