[SQL Server 2008] Problem with Between syntax

  • Hi there, hope in your help.

    In SQL server I have the column doTableDate set a Datetime.

    I need extract all rows in on date range and I think use to syntax `Between And`

    If try this version of query I have in output 889 rows all with date 2014-01-03... but I have other records with date 2014-01-04 in column doTableData...

    SELECT

    *

    FROM

    doTable

    WHERE

    doTableDate BETWEEN CONVERT (

    datetime,

    '03/01/2014 00:00:00',

    103

    )

    AND CONVERT (

    datetime,

    '04/01/2014 00:00:00',

    103

    );

    If try this version I don't have output no record, why?

    The syntax `Between And` not working in SQL Server?

    Can you help me?

    Thank you in advance.

    SELECT

    *

    FROM

    doTable

    WHERE

    doTableDate BETWEEN CONVERT (

    datetime,

    '03/01/2014 00:00:00',

    103

    )

    AND CONVERT (

    datetime,

    '03/01/2014 00:00:00',

    103

    );

  • The second query will only return rows where the doTableDate is exactly 03/01/2014 00:00:00, because the upper and lower limits of the BETWEEN are the same

    What do you want it to return? What does the data look like?

    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
  • Never use BETWEEN on dates or datetimes; instead, use >= and < the next date/time value.

    Also, for literal dates, always use format 'YYYYMMDD', which is 100% unambiguous. '03/01/2014' could be Mar 1 or Jan 3, depending on where you are.

    --examples::

    --to get all rows for the jan 3 2014

    WHERE

    doTableDate >= '20140103' AND

    doTableDate < '20140104'

    --to get all rows for the month of feb 2014

    WHERE

    doTableDate >= '20140201' AND

    doTableDate < '20140301'

    --to get all rows for the hour of 8am on mar 12 2014

    WHERE

    doTableDate >= '20140312 08:00' AND

    doTableDate < '20140312 09:00'

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • ScottPletcher (5/15/2015)


    Never use BETWEEN on dates or datetimes;

    Out of curiosity, why do you say this?

    I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...

  • Jason A. Long (5/15/2015)


    ScottPletcher (5/15/2015)


    Never use BETWEEN on dates or datetimes;

    Out of curiosity, why do you say this?

    I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...

    One reason I can come up with is that it is easy to calculate the start date of each month.

    A second reason would be if the data type is changed from DATE to DATETIME you wouldn't have to change any code to keep it working correctly.

  • Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...

  • Jason A. Long (5/15/2015)


    Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...

    Some people are more adamant than others.

  • The following is what I'd put in the NEVER do category...

    WHERE CAST(t.SomeDateTime as DATE) BETWEEN @BegDate and @EndDate

    Actually... I should say "Never do again" category... I have a few report procs that I still need to clean up... :unsure:

  • Jason A. Long (5/15/2015)


    The following is what I'd put in the NEVER do category...

    WHERE CAST(t.SomeDateTime as DATE) BETWEEN @BegDate and @EndDate

    Actually... I should say "Never do again" category... I have a few report procs that I still need to clean up... :unsure:

    Just curious, why?

  • Lynn Pettis (5/15/2015)


    Jason A. Long (5/15/2015)


    Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...

    Some people are more adamant than others.

    Here's why you NEVER code it with BETWEEN:

    what is the between code to get all of February, for say the current year?

    Missing data when a column changes from datetime to datetime2 and for certain other data type changes.

    Whereas the >= and < method works accurately in all those situations.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...

    Just tetested with this...

    DECLARE

    @BegDate DATE = '2015-01-10',

    @EndDate DATE = '2015-05-15'

    SELECT

    tt.DateAndTime

    FROM dbo.TestTime tt

    WHERE

    tt.DateAndTime >= @BegDate

    AND tt.DateAndTime < @EndDate

    SELECT

    tt.DateAndTime

    FROM dbo.TestTime tt

    WHERE

    CAST(tt.DateAndTime AS DATE) BETWEEN @BegDate AND @EndDate

    They both produced the exact same execution plan... I'm feeling like a moron... So I'll shut up now (and take it off the "never" list Monday morning)... :Whistling:

  • ScottPletcher (5/15/2015)


    Lynn Pettis (5/15/2015)


    Jason A. Long (5/15/2015)


    Both good points... Although I'd still put this in the "good idea not to do this" category as opposed to the "never do this" category...

    Some people are more adamant than others.

    Here's why you NEVER code it with BETWEEN:

    what is the between code to get all of February, for say the current year?

    Missing data when a column changes from datetime to datetime2 and for certain other data type changes.

    Whereas the >= and < method works accurately in all those situations.

    The problem is using the word NEVER. I understand and agree that for date ranges you should use DateColumn >= StartRange and DateColumn < EndRange.

    I just won't use the term NEVER when expressing my opinion on this subject. It is like say never use cursors.

  • Jason A. Long (5/15/2015)


    WOW... I could have sworn that the last time I tested, CASTing from a DATETIME to a DATE made it unsargable...

    Just tetested with this...

    DECLARE

    @BegDate DATE = '2015-01-10',

    @EndDate DATE = '2015-05-15'

    SELECT

    tt.DateAndTime

    FROM dbo.TestTime tt

    WHERE

    tt.DateAndTime >= @BegDate

    AND tt.DateAndTime < @EndDate

    SELECT

    tt.DateAndTime

    FROM dbo.TestTime tt

    WHERE

    CAST(tt.DateAndTime AS DATE) BETWEEN @BegDate AND @EndDate

    They both produced the exact same execution plan... I'm feeling like a moron... So I'll shut up now (and take it off the "never" list Monday morning)... :Whistling:

    I would still refrain from using BETWEEN for date ranges. Biggest reason being if there is a data type change to column such as DATE to DATETIME or DATETIME to DATETIME2.

    The reason I questioned your initial statement was for just the reason you discovered. Sometimes using a function does NOT mean the comparision becomes nonSARGable.

  • Lynn Pettis (5/15/2015)


    I would still refrain from using BETWEEN for date ranges. Biggest reason being if there is a data type change to column such as DATE to DATETIME or DATETIME to DATETIME2.

    The reason I questioned your initial statement was for just the reason you discovered. Sometimes using a function does NOT mean the comparision becomes nonSARGable.

    Oh I knew right away what you were driving at. That's why I did a retest after reading your comment.

    That's that's the strange thing... I'd always worked with the assumption that casting a datetime to date didn't affect sargabilty... Then a couple weeks a ago, I came a cross a forum discussion (different forum) that made me 2nd guess that assumption so I did a few tests... Obviously the tests were flawed because I swear I saw the execution plan go from an index seek to an index scan. At this point I just wish I'd held on to the test code so that I figure out where I'd screwed up.

    Anyway, thank you both for setting me straight.

  • Jason A. Long (5/15/2015)


    ScottPletcher (5/15/2015)


    Never use BETWEEN on dates or datetimes;

    Out of curiosity, why do you say this?

    I could see it it you were talking about DATETIME data types only, simply because of the logical "<=" on the EndDateTime, but I don't see how it's an issue with the DATE data type...

    I can't speak for Scott but my answer would be, why do it two different ways when 1 way will work very well for both. I've also seen it when people finally make the realization of the huge mistake they made when they separated DATE and TIME into separate columns and recombine them further down the line. That, of course, means that they have to go back and find all of the BETWEEN code and change it to the >=/< code. It's much safer just to stay in that habit to begin with.

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

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

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