SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


compare varchar dates


compare varchar dates

Author
Message
dubem1-878067
dubem1-878067
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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'


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26450 Visits: 17557
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.

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)
RP_DBA
RP_DBA
SSC Eights!
SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)SSC Eights! (947 reputation)

Group: General Forum Members
Points: 947 Visits: 1070
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
dubem1-878067
Valued Member
Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)Valued Member (58 reputation)

Group: General Forum Members
Points: 58 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?
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26450 Visits: 17557
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.

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)
mister.magoo
mister.magoo
SSCarpal Tunnel
SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)SSCarpal Tunnel (4.2K reputation)

Group: General Forum Members
Points: 4176 Visits: 7865
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);




  • 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

  • Phil Parkin
    Phil Parkin
    SSCoach
    SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)SSCoach (18K reputation)

    Group: General Forum Members
    Points: 18907 Visits: 20460
    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

    Please surround any code or links you post with the appropriate IFCode formatting tags. It helps readability a lot.
    MMartin1
    MMartin1
    SSCrazy
    SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

    Group: General Forum Members
    Points: 2813 Visits: 2031
    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).

    ----------------------------------------------------
    How to post forum questions to get the best help
    Eugene Elutin
    Eugene Elutin
    SSCertifiable
    SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

    Group: General Forum Members
    Points: 5086 Visits: 5478
    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
    MMartin1
    MMartin1
    SSCrazy
    SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

    Group: General Forum Members
    Points: 2813 Visits: 2031
    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))




    ----------------------------------------------------
    How to post forum questions to get the best help
    Go


    Permissions

    You can't post new topics.
    You can't post topic replies.
    You can't post new polls.
    You can't post replies to polls.
    You can't edit your own topics.
    You can't delete your own topics.
    You can't edit other topics.
    You can't delete other topics.
    You can't edit your own posts.
    You can't edit other posts.
    You can't delete your own posts.
    You can't delete other posts.
    You can't post events.
    You can't edit your own events.
    You can't edit other events.
    You can't delete your own events.
    You can't delete other events.
    You can't send private messages.
    You can't send emails.
    You can read topics.
    You can't vote in polls.
    You can't upload attachments.
    You can download attachments.
    You can't post HTML code.
    You can't edit HTML code.
    You can't post IFCode.
    You can't post JavaScript.
    You can post emoticons.
    You can't post or upload images.

    Select a forum

































































































































































    SQLServerCentral


    Search