CODEWITHSUNDEEP
sqlsql-serverdatediff
WhiskerBiscuit
WhiskerBiscuit

Reputation: 5157

Why am I getting different results when using the DATEDIFF function with a different specified interval?

I'm running the following queries using the DATEDIFF function using the YEAR and MONTH intervals, but modifying the right side of the query accordingly so that I thought they should be identical. dbo.logs has 2486512 rows.

SELECT count(*) FROM dbo.logs                  
WHERE DATEDIFF(YEAR, DT, GetDate()) <= 1;

result: 595712

SELECT count(*) FROM dbo.logs                  
WHERE DATEDIFF(MONTH, DT, GetDate()) <= 12;

result: 334773

Why am I getting such drastically different results? I would think specifying 12 months would return the same number of rows as 1 year.

Upvotes: 0

Views: 504

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269673

DATEDIFF() doesn't quite do what you expect. It counts the number of boundaries between two date/times. So, the year difference for both the following is "1":

2018-12-31  and   2019-01-01
2018-01-01  and   2019-12-31

The month differences vary between 1 and 23.

A much better approach is to use date arithmetic:

WHERE DT <= DATEADD(YEAR, -1, GETDATE())

Not only is this more accurate, but it can also take advantage of an index.

Upvotes: 1

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

It gives year difference only

SELECT  DATEDIFF(YEAR,'2017-02-02', GetDate())  
Result-2

It gives month difference only.

SELECT  DATEDIFF(MONTH,'2019-02-02', GetDate())

Result-5

Upvotes: 0

Related Questions