BETWEEN query on varchar

[overDeveloper]

I need to do a between query on a varchar field that looks like, for example, 12 DEC 2007

so something like ...where field1 between '12 DEC 2007' and '12 JAN 2008'

is there a way to do this?

 

[AlexCuse]

No.

If you can CAST as date/time then maybe you can, but this will cause errors if you've got bad data. Really what you need to do is store your Dates in the proper column type.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[gmmastros]

If you keep this as a string, the between won't work properly. You'll need to convert this to a date and then do the between.

Ex

Where Field1 Between Convert(DateTime, '12 DEC 2007') and Convert(DateTime, '12 JAN 2008')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Crowley16]

well, technically, if you format your date string into ansi format, i.e. '2007-12-12' then you can.

however, you definitely should still store dates as datetime, not varchar...

--------------------
Procrastinate Now!