date comparison

Question

date comparison

pramod.chauhan

SSC Journeyman

Points: 91
More actions
November 5, 2008 at 6:58 am

#383310

column process_date datatype is DateTime
select * from tablea where employeeid = '00880187'
and process_date=convert(datetime,'2008-10-31 00:00:29.000',106)
Why above query gives following error
"Syntax error converting datetime from character string"

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

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 1

pramod.chauhan (11/5/2008)
column process_date datatype is DateTime
select * from tablea where employeeid = '00880187'
and process_date=convert(datetime,'2008-10-31 00:00:29.000',106)
Why above query gives following error
"Syntax error converting datetime from character string"

select * from tablea where employeeid = '00880187'

and process_date='2008-10-31 00:00:29.000'

Madhivanan

Failing to plan is Planning to fail

pramod.chauhan SSC Journeyman Points: 91 More actions · Answer 2

thanks for quick reply.

When i am converting a string into datetime for comparing it with a column which is datetime why is it giving error,

Andrew..Peterson SSCertifiable Points: 6838 More actions · Answer 3

with the convert statement, you have the third optional value, which tells the convert statement how to format the value which is for character converts, not datetime.

So drop the 106, third value.

The above suggestion uses implied conversion, you are trying to to explicit, which is OK, just ditch the 106.

The more you are prepared, the less you need it.

Jeff Moden SSC Guru Points: 1004434 More actions · Answer 4

pramod.chauhan (11/5/2008)
thanks for quick reply.
When i am converting a string into datetime for comparing it with a column which is datetime why is it giving error,

You could use the 3rd operand... but ya gotta use the right one. 106 is for the "dd mon yy" format... you didn't supply the string date in that format.

As the others have stated, no conversion necessary.

BUT! Because of the format you selected, I'm thinking that you're looking for a "date with no time" comparison... would that be true?

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

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 5

If you want to omit time part

select * from tablea where employeeid = '00880187'

and process_date>=dateadd(Day,datediff(day,0,'2008-10-31 00:00:29.000'),0)

and process_date<dateadd(Day,datediff(day,0,'2008-10-31 00:00:29.000'),1)

Madhivanan

Failing to plan is Planning to fail