I have table containing one datetime column. I need to return rows for only last 6 months. This can be done by
where datetime_column > DATEADD(m, -6, current_timestamp)
But how to extend this option if I want to return latest month beginning with first day of the month? E.g. I run this condition in the middle of month (14/6/2000)
, the latest row is set to 14/1/2000
, but i would like to return it as 1/1/2000
. Any advice?
I tried some subqueries (max function of datetime including month function) but with no success.
Instead of approximating the "current" date by selecting the MAX(date) the code could reference CAST(GETDATE() as DATE) to access the system datetime and cast it as type DATE. where [date] > dateadd(month, -6, cast(getdate() as date));
In SQL Server, you can use the DATEADD() function to get last 3 months (or n months) records.
SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) AS [month] FROM R when above sql execute how it is getting value for -N ??
SELECT * FROM product WHERE pdate >= DATEADD(day, -30, getdate()).
For MS SQL Server, you can use:
where datetime_column >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -6, current_timestamp)), 0)
where datetime_column > dateadd(m, -6, getdate() - datepart(d, getdate()) + 1)
where datetime_column > curdate() - interval (dayofmonth(curdate()) - 1) day - interval 6 month
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