CODEWITHSUNDEEP
sql-server
fishmong3r
fishmong3r

Reputation: 1434

What is the difference between date handling in my queries?

I need the number of records of last month. I used to use this approach so far:

select 
 count(ID)
from 
 table
where
 col1 = 'somerule'
and 
 DateTimeOfInsert >= '20150901' 
and 
 DateTimeOfInsert <= '20150930'

Now I'm about to atomate this task and therefore I have to pull the start and end dates of last month automatically. So here it is:

select 
 count(ID)
from 
 table
where
 col1 = 'somerule'
and 
 DATEPART(m, DateTimeOfInsert) = DATEPART(m, DATEADD(m, -1, getdate()))
and 
 DATEPART(yyyy, DateTimeOfInsert) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

My only issue is that at this very moment the first query returns 1073 and the second one 1124. So the question is obvious: what is the difference between them? Both should inlude the start and end date. I can't spot it.

Upvotes: 0

Views: 29

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

This condition:

DateTimeOfInsert >= '20150901' and  DateTimeOfInsert <= '20150930'

retrieves record that are between 2015-09-01 00:00:00.000 and 2015-09-30 00:00:00.000.

If DateTimeOfInsert is DATETIME, then this will return different result from your other condition.

The best way for this kind of queries is not to use BETWEEN but rather use >= and <. In your case, you want to get records for the last month, so you want to use >= start of last month and < start of this month:

DateTimeOfInsert >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 0)    -- Beginning of previous month
AND DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)  -- Beginning of this month

The above condition also makes your query sargable.

For some common date routines, see this article.

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

The difference is in time component of datetime.

This: 

DateTimeOfInsert >= '20150901' and DateTimeOfInsert <= '20150930'

will not select date like 20150930 15:30.

But this:

DATEPART(m, DateTimeOfInsert) = DATEPART(m, DATEADD(m, -1, getdate()))

will select it because you are checking for months and year part only. That`s why the second select returns more rows.

Both queries will return the same if you just change the first statement so that it will consider time component of last day of month:

DateTimeOfInsert >= '20150901' and DateTimeOfInsert < '20151001'

Upvotes: 2

Related Questions