SQL Date Range Query Issue

Question

I am writing a SQL query to return matching items based on a set of criteria, one of which is to check todays date against a date range - stored as two seperate columns 'DateFrom' and 'DateTo'

My Query is as follows:

SELECT  
    A.* 
FROM 
    A 
LEFT OUTER JOIN 
    B ON A.AlertID = B.AlertID 
WHERE
    A.AlertTypeID > 1 AND A.Active = 1 AND
    (B.Complete IS NULL OR B.Complete < 1) AND
    (A.DateFrom IS NULL OR 
    ((CONVERT(datetime, A.DateFrom, 103) <= CONVERT(datetime, GETDATE(), 103) AND 
      CONVERT(datetime, A.DateTo, 103) >= CONVERT(datetime, GETDATE(), 103))))

This works fine if DateFrom and DateTo are different, but the issue I have is that I have an entry which has the same value in each, and it should be returned, but it is not.

Please can anyone advise on this one. Thanks.

`CONVERT(datetime, GETDATE(), 103)` is the same value as `GETDATE()` what is the goal here? — JamieD77, Feb 24 '16 at 16:13
@JamieD77 - My apologies, I didn't see the `datetime` in `convert` — Pரதீப், Feb 24 '16 at 16:19
AND THEREIN LIES THE PROBLEM @MotoGP. My code should be: (CONVERT(datetime, CONVERT(varchar, A.DateFrom, 103), 103) >= CONVERT(datetime, CONVERT(varchar, GETDATE(), 103), 103) AND CONVERT(datetime, CONVERT(varchar, A.DateTo, 103), 103) <= CONVERT(datetime, CONVERT(varchar, GETDATE(), 103), 103))) — Richard Gale, Feb 24 '16 at 16:21
@Richard.Gale instead you can do this `cast(getdate() as date)` — Pரதீப், Feb 24 '16 at 16:23

score 2 · Accepted Answer · answered Feb 24 '16 at 16:32

Okay, so it was my own stupid fault, I normally cast the date to a varchar to trim off the time part, but as @MotoGP has now shown me a cleaner method for this, which I have gratefully adopted, my code now works and looks as follows:

    SELECT  
        A.* 
    FROM 
        A 
    LEFT OUTER JOIN 
        B ON A.AlertID = B.AlertID 
    WHERE
        AlertTypeID > 1 AND A.Active = 1 AND
        (B.Complete IS NULL OR B.Complete < 1) AND
        (A.DateFrom IS NULL OR 
        (CAST(A.DateFrom AS Date) >= CAST(GETDATE() AS Date) AND CAST(A.DateTo AS Date) <= CAST(GETDATE() AS Date)))

see http://stackoverflow.com/questions/25564482/how-to-compare-datetime-with-only-date-in-sql-server/25564539#25564539 — Steve Ford, Feb 24 '16 at 16:39

score 1 · Answer 2 · answered Feb 24 '16 at 16:22

The problem is CONVERT(datetime, GETDATE(), 103), if you run this:

SELECT CONVERT(DATETIME, GETDATE(), 103)

You get:

2016-02-24 16:19:57.840

When I guess you only want to compare the date portion of the value, so you actually want:

2016-02-24

So use DATE instead in your convert:

SELECT CONVERT(DATE, GETDATE(), 103)

This is only a quick mock-up of a piece of code using this logic, please test using your own data to verify:

CREATE TABLE #temp
    (
      id INT ,
      datefrom DATETIME ,
      dateto DATETIME
    )

INSERT  INTO #temp
        ( id, datefrom, dateto )
VALUES  ( 1, '2016-02-02', '2016-02-28' ), -- will match
        ( 2, '2016-02-27', '2016-02-28' ), -- won't match
        ( 3, '2016-02-23', '2016-02-23' ), -- yesterday no match
        ( 4, '2016-02-24', '2016-02-24' )  -- today matches

SELECT  *
FROM    #temp A
WHERE   A.datefrom IS NULL
        OR ( (CONVERT(DATE, A.datefrom, 103) <= CONVERT(DATE, GETDATE(), 103)
             AND CONVERT(DATE, A.dateto, 103) >= CONVERT(DATE, GETDATE(), 103))
           )

DROP TABLE #temp

Produces:

id  datefrom                dateto
1   2016-02-02 00:00:00.000 2016-02-28 00:00:00.000
4   2016-02-24 00:00:00.000 2016-02-24 00:00:00.000

Thanks for your assistance, seems I causes the issue myself by not having my complete conversion in place to ensure I was only comparing dates (not date and time) — Richard Gale, Feb 24 '16 at 16:34

SQL Date Range Query Issue

2 Answers2