Skip to content
Advertisement

SQL select records with current month

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))
User contributions licensed under: CC BY-SA
3 People found this is helpful
Advertisement