CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
yosh
yosh

Reputation: 3305

SQL - Proper "where" clause for current month

Which is the proper way of checking events from current month on SQL Server and why?

1) WHERE (DATEDIFF(month, EventTime, GETDATE())=0))

2) WHERE (YEAR(EventTime) = YEAR(GETDATE()) AND MONTH(EventTime) = MONTH(GETDATE()))

Date format in table is i.e. EventTime: 2011-11-30 15:68:25.000

Upvotes: 1

Views: 16100

Answers (5)

Marcelo Rebouças
Marcelo Rebouças

Reputation: 699

One tip very simple using to_char function, look:

At date example:

2011-11-30 15:68:25.000

you can do this:

to_char(your_field , 'MM') = 11

or for current month of system date:

to_char(sysdate , 'MM') = 11

One detail, to_char funcion is suported by sql language and not by one specific database.

Upvotes: 0

Raj More
Raj More

Reputation: 48018

If you have an index on that column, then both calculations are bypassing the index because you're not using what the column is indexed on, but only a part of the index

WHERE (DATEDIFF(month, EventTime, GETDATE())=0))
WHERE (YEAR(EventTime) = YEAR(GETDATE()) AND MONTH(EventTime) = MONTH(GETDATE()))

You are much better off using something like this

WHERE EventTime BETWEEN Cast (DATEADD(dd,-(DAY(GetDate())-1),GetDate()) as Date)
                AND Cast (DATEADD(dd,-(DAY(DATEADD(mm,1,GetDate()))),DATEADD(mm,1,GetDate())) as Date)

You can also use the same concept with a >= and <= for the dates

Upvotes: 1

MatBailie
MatBailie

Reputation: 86706

I don't have access to a SQL Server with a profiler, so I can't actually give as detailed an answer as I'd like.

The question is basically about which one allows the least calculation and most effective use of indexes.

Variants that use string manipulation have the highest calculation load, and don't use indexes at all. So I'll just skip those. That leave four common expressions...

SELECT * FROM date_sargable
WHERE  YEAR(value) = YEAR (getDate())
  AND MONTH(value) = MONTH(getDate())
;

SELECT * FROM date_sargable
WHERE DATEDIFF(MONTH, value, getDate()) = 0
;

SELECT * FROM date_sargable
WHERE DATEDIFF(MONTH, 0, value) = DATEDIFF(MONTH, 0, getDate())
;


SELECT * FROM date_sargable
WHERE value >= DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate())    , 0)
  AND value <  DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()) + 1, 0)
;

The first three use INDEX SCANs, but the last one uses an INDEX SEEK. The difference is that the format of the query allows the optimiser to know you want a specific range of the data, that it's all next to each other in one block of the index, and that it's very easy to find that block.

If, when looking at execution plans, you see a SEEK in one version, and a SCAN in another, you're much more likely to benefit from the SEEK.

Upvotes: 7

Markus Winand
Markus Winand

Reputation: 8706

Write queries for continuous periods as explicit range condition.

http://use-the-index-luke.com/sql/where-clause/obfuscation/dates

Thus, use something like this:

WHERE EventTime between <begin-of-month> and <end-of-month>

Example code is available at the same page, although doing quarterly filtering:

http://use-the-index-luke.com/sql/where-clause/obfuscation/dates?dbtype=sqlserver#sample_quarter_begin_end

Why? To make indexing easy.

Upvotes: 4

user359040
user359040

Reputation:

Logically, they both are.

However, I think the first one is simpler (one condition instead of two), easier to understand and more likely to be sargable - so I suggest using that one.

Upvotes: 1

Related Questions