Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

compare varchar dates Expand / Collapse
Author
Message
Posted Tuesday, February 12, 2013 2:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 9:54 PM
Points: 44, Visits: 158
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
Posted Tuesday, February 12, 2013 2:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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.

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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1419195
Posted Tuesday, February 12, 2013 2:46 PM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Thursday, November 20, 2014 2:30 PM
Points: 628, Visits: 870
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
Posted Tuesday, February 12, 2013 4:55 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 30, 2013 9:54 PM
Points: 44, Visits: 158
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
Posted Tuesday, February 12, 2013 4:56 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:31 PM
Points: 13,207, Visits: 12,688
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
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1419240
Posted Tuesday, February 12, 2013 5:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 6:19 PM
Points: 1,805, Visits: 5,864
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1419246
    Posted Wednesday, February 13, 2013 2:04 AM


    SSCertifiable

    SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

    Group: General Forum Members
    Last Login: Yesterday @ 4:18 AM
    Points: 5,245, Visits: 12,161
    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.



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

    When you ask a question (and please do ask a question: "My T-SQL does not work" just doesn't cut it), please provide enough information for us to understand its context.
    Post #1419349
    Posted Tuesday, May 7, 2013 12:16 PM


    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Yesterday @ 1:04 PM
    Points: 420, Visits: 996
    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
    Posted Wednesday, May 8, 2013 3:36 AM
    SSCrazy

    SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

    Group: General Forum Members
    Last Login: Tuesday, November 18, 2014 3:45 AM
    Points: 2,873, Visits: 5,188
    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!"
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help
    Post #1450472
    Posted Wednesday, May 8, 2013 11:02 AM


    SSC-Addicted

    SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

    Group: General Forum Members
    Last Login: Yesterday @ 1:04 PM
    Points: 420, Visits: 996
    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 »

    Add to briefcase

    Permissions Expand / Collapse