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'
No that won't work because your datatype is varchar. :angry:
You don't have to explicitly convert all 3 but you DO have to force it to datetime.
where convert(datetime, mydatecolumn, 120) between '2012-10-10' and '2013-04-01'
The problem here is that you have no chance of seeks. The best thing would be to change the datatype to datetime, of course that isn't always possible.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/