Jasmine
Jasmine

Reputation: 5326

Results missing data between two dates with BETWEEEN or >= and <=

Starting with...

SET @DATEFROM = '2014-04-01'
SET @DATETO = '2014-10-01'

..., I am trying to query with the following filters:

C.DateCreate BETWEEN @DATEFROM AND @DATETO)

or

C.DateCreate >= @DATEFROM AND C.DateCreate <= @DATETO

Both return the same result set, which does not include the current month's (October's) data.

I want the data for October. Apparently that is what I am setting. Is there a workaround to achieve this please?

Upvotes: 0

Views: 58

Answers (2)

J0e3gan
J0e3gan

Reputation: 8938

You are filtering for everything between April 1st and October 1st - and including nothing in October. Pretty simple really. Maybe you have been staring at this too long (seriously, not sarcastically).

Try this if you just want everything in October 2014:

SET @DATEFROM = '2014-10-01' -- or '2014-04-01' if you truly want to include data back to April
SET @DATETO = '2014-11-01'

-- .
-- .
-- .
C.DateCreate >= @DATEFROM AND C.DateCreate < @DATETO

Note that I replaced <= with < in the @DATETO comparison and set @DATETO to November 1 rather than October 31 - since the former is midnight on November 1 (i.e. anything less than this falling into October down to the last moment like you want).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270643

If you want October and you want to use those limits, I would recommend:

C.DateCreate >= @DATEFROM AND C.DateCreate < DATEADD(MONTH, 1, @DATETO)

Upvotes: 1

Related Questions