Syntax error converting datetime from character string.？

Question

Post reply

Syntax error converting datetime from character string.？

kelphen chen

Grasshopper

Points: 23
More actions
March 18, 2008 at 7:42 pm

#69996

i create a table and insert rows
create table T
(
dt varchar(10)
)
insert T select null
insert T select ''
insert T select '2003-07-01'
insert T select '2004-01-05'
insert T select 'SDS'
--select, no errors
select dt,Days
from (select dt,datediff(dd,'2003-07-01',dt) as Days from T where ISDate(dt)=1) Temp_A
--select, error
select dt,Days
from (select dt,datediff(dd,'2003-07-01',dt) as Days from T where ISDate(dt)=1) Temp_A
where Days> =0
Syntax error converting datetime from character string.
why?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 1

Man, you've got me... never saw such a thing.

But, riddle me this... why do you want the derived table when the query works fine without it?

select dt,datediff(dd,'2003-07-01',dt) as Days from T where ISDate(dt)=1

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Looks like a bug to me.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 3

... and it also returns the correct resultset before giving the error.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Huh. Good point Gogula (great name, my compliments to your parents). I was in grid mode and didn't notice it.

Looking through the optimizer, it appeared to me that it was hoisting the inner query to the outer, but forgetting to apply the correct conversion in the test.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Sergiy SSC Guru Points: 110208 More actions · Answer 5

rbarryyoung (3/19/2008)
Huh. Good point Gogula (great name, my compliments to your parents).

I wonder how Jeff got himself responding to somebody with a nickname like YOURS.

😀

Look at his signature!

:hehe:

_____________
Code for TallyGenerator