I have a field name textbook date field name NewMonth
The data like this
TextBook NewMonth ABC 2020-01-01 HDS 2020-01-30 ZXY 2020-02-15 FGD 2020-02-01 YTS 2020-04-02 HFH 2020-04-05 EDD 2020-03-25
My goal to select the records with current month (2020-04-XX)
TextBook NewMonth YTS 2020-04-02 HFH 2020-04-05
My query and not working. Can some one correct my query. Thank you
SELECT TextBook, NewMonth from Store where NewMOnth >= Dateadd(Month, Datediff(Month, 0, DATEADD(m, -1, current_timestamp)), 0)
I think -1 for current month, -2 for last 2 months , -3 for last 3 months andso on
Advertisement
Answer
My goal to select the records with current month (2020-04-XX)
Here is one option:
where NewMonth >= datefromparts(year(getdate()), month(getdate()), 1)
If you need an upper bound too:
where NewMonth >= datefromparts(year(getdate()), month(getdate()), 1) and NewMonth < dateadd(month, 1, datefromparts(year(getdate()), month(getdate()), 1))
If you want the previous month:
where NewMonth >= dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 1)) and NewMonth < datefromparts(year(getdate()), month(getdate()), 1)
Or two months ago:
where NewMonth >= dateadd(month, -2, datefromparts(year(getdate()), month(getdate()), 1)) and NewMonth < dateadd(month, -1, datefromparts(year(getdate()), month(getdate()), 1))