compare varchar dates

Question

compare varchar dates

dubem1-878067

Ten Centuries

Points: 1044
More actions
February 12, 2013 at 2:19 pm

#274116

I have a varchar column containing dates (not my design) with this format 2013-02-12
I 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'
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

dubem1-878067 (2/12/2013)
I have a varchar column containing dates (not my design) with this format 2013-02-12
I 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/

RP_DBA SSCertifiable Points: 5116 More actions · Answer 2

dubem1-878067 (2/12/2013)
but can I do simply to avoid 3 convert?
where mydatecolumn between '2012-10-10' and '2013-04-01'

Trying to eliminate the CONVERT every time you reference this column? Have you considered a computed column or a view?

--computed column

ALTER TABLE [sometable] ADD convMyDateColumn AS CONVERT(DATETIME,MyDateColumn);

-- view

CREATE VIEW vwTime

AS

SELECT MyDateColumn = CONVERT(DATETIME,MyDateColumn)

FROM [SomeTable];

_____________________________________________________________________
- Nate

@nate_hughes

dubem1-878067 Ten Centuries Points: 1044 More actions · Answer 3

Sean Lange (2/12/2013)
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'

Will this really work? Comparing a date with two varchar? Whitout converting the varchars?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

dubem1-878067 (2/12/2013)
Sean Lange (2/12/2013)
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'
Will this really work? Comparing a date with two varchar? Whitout converting the varchars?

Yes it will do an implicit conversion of the second two because of datatype precendence.

_______________________________________________________________

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/

mister.magoo SSC-Forever Points: 47174 More actions · Answer 5

dubem1-878067 (2/12/2013)
I have a varchar column containing dates (not my design) with this format 2013-02-12
I 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?

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

Phil Parkin SSC Guru Points: 246961 More actions · Answer 6

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-12
I 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.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

MMartin1 One Orange Chip Points: 27795 More actions · Answer 7

If working with a large data set, the convert function in the where clause will slow things down as compared to having a datetime column. It may be beneficial to import first the data into a temp table, especially if you are using a linked server connection, and do the conversion during the insert. Then use the temp table as your base recordset.

Also keep the yyyy-mm-dd standard. If you switch to mm-dd-yyyy it can cause problems depending on your default language. (In Europe they interpret as dd-mm-yyyy).

----------------------------------------------------

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 8

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-12
I 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.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

MMartin1 One Orange Chip Points: 27795 More actions · Answer 9

I, personally, would change datatype in the table.

Use a bit of caution, this may affect other views and stored procedures that use this column. They may, for instance, be using a substring function to get the month string, then use this string in a weird way elsewhere. Another thing you can do is just add a the datetime version of this column to the table. You still have to watch out for inserts that dont spell out the columns being inserted into and select *'s in code elsewhere.

For illustration : This works only when the variable is declared as varchar >

declare @d datetime --varchar(30)

set @d='2012-03-02'

Select (substring(@d,6,2))+'/'+(substring(@d,1,4))

----------------------------------------------------