comparing to datetime column

[zinkjo]

what is the best way to compare a constant such as '07-21-2004' to a datetime column

I want something like the following
select * from tableA where myDateCol = '07-21-2004'

if myDateCol has a value of '07-21-2004 10:15:00' the row should be selected. I want the query to ignore the time portion.

 

[amorous]

Try this:

select * from tableA where Convert(varchar(10),myDateCol,110) = '07-21-2004'

-VJ

 

[zinkjo]

The only problem with that is that it does a character comparison. so if the constant has '7-21-2004' instead of '07-21-2004' the row will not return.

 

[amorous]

Try this:

select * from tableA where Convert(varchar(10),Convert(datetime,myDateCol),110) = '07-21-2004'

-VJ

 

[zhavic]

Than use this: ( just playing with CONVERT and CAST functions )


select * from tableA where Convert(varchar(10),myDateCol,110) = Convert(varchar(10), Cast( '7-21-2004' as datetime ),110)


Than you can use '7-21-2004' or '07-21-2004'

Zhavic



---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.