Menu
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.
789
We can retrieve the current month value in SQL using the MONTH() and DATEPART() functions along with the GETDATE() function. To retrieve the name of the month functions in SQL such as DATENAME() and FORMAT() are used.
Just run these SQL queries one by one to get the specific element of your current date/time: Current year: SELECT date_part('year', (SELECT current_timestamp)); Current month: SELECT date_part('month', (SELECT current_timestamp)); Current day: SELECT date_part('day', (SELECT current_timestamp));
select * from orders where order_date>now() - interval 1 month; In the above query, we select rows after past 1 month interval. We use INTERVAL clause and NOW() function to obtain the date 1 month in the past, from present date.
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)
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!)
20
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
