Select records between a date range

  • This is my query:

    SELECT * FROM [MyDateTable]

    WHERE CONVERT(VARCHAR,StartDate,101)

    BETWEEN '02/01/2007' AND '01/04/2008'

    It return absolutely nothing, although there are eleven eligible records within this date range. few in Feb 2007 and others in May 2007.

    What could be wrong in this?

  • It's because you're converting the dates to text... try this, instead...

    SELECT * FROM [MyDateTable]

    WHERE StartDate >= '02/01/2007'

    AND StartDate < '01/05/2008'

    Notice that the "<" is in relation to the NEXT day after you want... this is to allow all the times on 01/04/2008 to be included without a conversion to a whole date. This method also allows for the use of an index "seek" whereas your method will not.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Don't convert your dates to string - convert your strings to dates. As of right now you're comparing string values (where '02/01/2007' >'01/28/2008').

    you want:

    SELECT * FROM [MyDateTable]

    WHERE StartDate

    BETWEEN cast('02/01/2007' as datetime)

    AND cast('01/04/2008' as datetime)

    of course - you can also let T-SQL do the implicit conversion for you with:

    SELECT * FROM [MyDateTable]

    WHERE StartDate

    BETWEEN '02/01/2007' --this gets converted to a date

    AND '01/04/2008' --this gets converted to a date

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Perfect, Matt! That's just what I wanted. Figured it out only after i hurriedly posted the query 🙂

    Thanks, Jeff!

  • No... it's not perfect if you want to include the whole day of 01/04/2008...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • What's perfect is: 'Don't convert your dates to string - convert your strings to dates'

    That's all i needed 🙂 cuz the query I've written uses the comparison operators '>=' and '<='.. which is why I said: 'Thanks, Jeff'!

  • You're missing the point... you will not get all of 01/04 using BETWEEN...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Ok.. Here's my query:

    SELECT * FROM [MyDateTable]

    WHERE StartDate >= CONVERT(DATETIME, '02/01/2007')

    AND StartDate <= CONVERT(DATETIME, '01/04/2008')

    This works fine, Jeff! Which is why I was saying, I used Matt's ''don't convert your date field to string'' and I used your ' operators instead of the BETWEEN'.

    Do you think I've gotten the point now? 🙂

  • No...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Unless none of your dates have times on them, the original query and the replacement query are bad...

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Nisha (1/4/2008)


    Ok.. Here's my query:

    SELECT * FROM [MyDateTable]

    WHERE StartDate >= CONVERT(DATETIME, '02/01/2007')

    AND StartDate <= CONVERT(DATETIME, '01/04/2008')

    This works fine, Jeff! Which is why I was saying, I used Matt's ''don't convert your date field to string'' and I used your ' operators instead of the BETWEEN'.

    Do you think I've gotten the point now? 🙂

    Yes, except that if there's a row with a start time of midnight on the 4th you will get it, but you won't get any other records for the 4th. (eg 2008/01/04 00:01, 2008/01/04 07:51, ...)

    If you need all the rows that have startdates on the 4th, then use

    StartDate < CONVERT(DATETIME, '01/05/2008')

    If you don't need anything from the 4th, then use

    StartDate < CONVERT(DATETIME, '01/04/2008')

    Datetimes are date and time, and you need to take that into account with your queries.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ok tell me exactly what I should do, Jeff.. I'm in trouble here then..!

  • Silly me!! Haha! Ok now i get what you guys are trying to say! I didn't pay much attention to that cuz I don't have any records for the 4th!

    Silly silly!

    Thanks a ton, guys!

    This was great!

  • Pleasure. Are you all sorted now?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Totally sorted out!!

    You know most of the times, mistakes are out of pure carlessness or oversight rather than lack of knowledge.. I can't always say that for me cuz I'm only a fresher right out of college, at my first job! Haha.

    But even at such a young age I'm subject to a great amount of pressure and I always seem to be too 'hurried' to read things right! Its terrible!

    Which is why, being here is such a great boon!

    Thanks again, guys!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply