Phil Parkin (2/13/2013)
mister.magoo (2/12/2013)
dubem1-878067 (2/12/2013)
I have a varchar column containing dates (not my design) with this format 2013-02-12I need to extract records between two dates
this code will do the job
where convert(datetime, mydatecolumn, 120) between convert(datetime, '2012-10-10',120) and convert(datetime, '2013-04-01',120]
but can I do simply to avoid 3 convert?
where mydatecolumn between '2012-10-10' and '2013-04-01'
I would say yes.
you will end up comparing three char based strings which, because of the reverse notation yyyy-mm-dd will compare quite nicely.
There is no need to convert to dates at all - or am I missing something here?
I was thinking the same thing. Might even be able to use an index.
Although it will definitely work for required comparison, it may give bad misconception that dates stored as varchar is ok and works fine regardless of situation. However it's not true.
When date is stored as varchar, you have no default guarantee that all values will be in the same, appropriate ISO format.
I guss OP can use it, but shouldn't take it as a good practice. I, personally, would change datatype in the table.