date field select help

Help, I've got a field in this format that I can't break apart.

05/30/2005 00:00 00

I need to select records with this field greater than 02/01/2006 without disturbing the field, can i do this select in SQL ? Even with the hours/seconds in there?

Thanks,

Scooter

select Convert(datetime, STUFF ('05/30/2005 00:00 00',17,1,':'), 101)

from the inside out. . .
replace the 17th character in a given string with a ':'
use convert function to change to a datetime, interpreting the output of the stuff with format 101

|||But how do i select the records greater than 02/01/2006?|||select * from theTable
where Convert(datetime, STUFF (theDateField,17,1,':'), 101) >
convert(datetime, '02/01/2006', 101)|||

How about:

SELECT *

FROM Table1

where cast(substring('05/30/2005 00:00 00',0,11) as datetime) > '05/30/2005'

It's a bit simpler. You don't need to convert the constant '02/01/2006' to a datetime as it is recognised as a valid date time format. Simply replace '05/30/2005 00:00 00' with your field name.

HTH

For more SQL Tips, check out my blog:

|||

doesn't that depend on the localization settings?

in the states 12/1/2006 is December first 2006 . . . but in the uk its January first. . . isnt it?

|||Yes, much simpler, Thank you very much to both of you !!