CODEWITHSUNDEEP
sql-servert-sqldatetime
korrowan
korrowan

Reputation: 583

TSQL retrieve all records in current month/year

I have a datetime field called DateFinished. I need to be able to retrieve all records in which DateFinished is within the current month/year.

Upvotes: 11

Views: 30069

Answers (3)

Aaron C
Aaron C

Reputation: 375

So the problem with @Bridge's method is use of index. @Moose & @PCurd's method has a problems depending on how the data is stored.

@PCurd's method would work fine if all data collected on a day is rounded down to that day. E.g. event at 5pm is recorded as 2021-11-30 00:00:00. But if time is kept (which is assumed as it is a datetime field in Ops situation) then this data will be lost.

So you need to use the <> operators.

SELECT * 
FROM MyTable
WHERE DateFinished >= 
     DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
     AND DateFinished <
     DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

For the method using datefromparts: SQL select records with current month

Upvotes: 0

Moose
Moose

Reputation: 5422

Just as an alternative - this should use an index on DateFinished.

SELECT * 
FROM MyTable
WHERE DateFinished BETWEEN 
     DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
     AND 
     DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)

Upvotes: 13

Bridge
Bridge

Reputation: 30711

If you've only got a small number of rows, this will do to get all rows where DateFinished is in this month of this year.

SELECT * 
FROM MyTable
WHERE Year(DateFinished) = Year(CURRENT_TIMESTAMP) 
                 AND Month(DateFinished) = Month(CURRENT_TIMESTAMP)

This could get quite slow over a large number of rows though - in which case using DateAdd, DatePart and BETWEEN is probably more appropriate, and can take advantage of indexes (I don't have time to write an answer involving those right now!)

Upvotes: 20

Related Questions