CODEWITHSUNDEEP
sqlsql-servert-sqlwhere-clausedate-arithmetic
user1203397
user1203397

Reputation: 67

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

Upvotes: 0

Views: 7987

Answers (2)

MOHAMMED JH
MOHAMMED JH

Reputation: 1

For 2020-04 you can use this :

WHERE CONCAT(YEAR(NewMonth),MONTH(NewMonth)) = CONCAT('2020','04')

To select records with current month :

WHERE CONCAT(YEAR(NewMonth),MONTH(NewMonth)) = CONCAT(now(),now())

Upvotes: -2

GMB
GMB

Reputation: 222402

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))

Upvotes: 5

Related Questions