compare varchar dates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » compare varchar dates

compare varchar dates

Rate Topic

Display Mode

Topic Options

Author

Message

dubem1-878067

Posted Tuesday, February 12, 2013 2:19 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 10:08 AM
Points: 43, Visits: 151

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'

Post #1419186

Sean Lange

Posted Tuesday, February 12, 2013 2:36 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1419195

RP_DBA

Posted Tuesday, February 12, 2013 2:46 PM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 12:31 PM
Points: 580, Visits: 814

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

Post #1419200

dubem1-878067

Posted Tuesday, February 12, 2013 4:55 PM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, February 14, 2013 10:08 AM
Points: 43, Visits: 151

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?

Post #1419239

Sean Lange

Posted Tuesday, February 12, 2013 4:56 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 8:46 AM
Points: 8,547, Visits: 8,204

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs

Post #1419240

mister.magoo

Posted Tuesday, February 12, 2013 5:24 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 4:46 PM
Points: 1,287, Visits: 3,850

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

Post #1419246

Phil Parkin

Posted Wednesday, February 13, 2013 2:04 AM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 5:12 AM
Points: 4,226, Visits: 9,458

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 was thinking the same thing. Might even be able to use an index.

____________________________________________________________________________________________

Help us to help you. For better, quicker and more focused answers to your questions, consider following the advice in this link:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

If you are asking for help and your post does not contain a question, you should expect responses which do not contain any answers. Put a question mark in there somewhere - it's not rocket science.

Post #1419349

mmartin1

Posted Tuesday, May 07, 2013 12:16 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:43 PM
Points: 192, Visits: 640

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).

Post #1450289

Eugene Elutin

Posted Wednesday, May 08, 2013 3:36 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:59 AM
Points: 2,525, Visits: 4,324

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 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!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1450472

mmartin1

Posted Wednesday, May 08, 2013 11:02 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 4:43 PM
Points: 192, Visits: 640

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))

Post #1450699

« Prev Topic | Next Topic »

Permissions