CODEWITHSUNDEEP
sqlsql-serverdatet-sql
cai120
cai120

Reputation: 29

Getting all data from last month

I am building an SQL view which shows me all entries of table T where the information is last month. I want it so that if I run the view any time in August, it will show me all entries for July, not just a month before which is what I have done with my current code.

Please see this:

where cast(t.Ticket_OpenDate as date) >= cast(dateadd(month, -1, getdate()) as date)

I look forward to hearing from someone.

Upvotes: -1

Views: 88

Answers (2)

Charlieface
Charlieface

Reputation: 71144

Using DATEDIFF as in the other answer will not perform well because it cannot use indexes (it is not sarge-able).

It is much better to use a date interval (start and end), in this case we want a half-open interval (exclusive end date):

WHERE t.Ticket_OpenDate >= DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()) - 1, 1)
  AND t.Ticket_OpenDate <  DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE())    , 1)

Upvotes: 3

buddemat
buddemat

Reputation: 5301

You should be able to use the DATEDIFF() function for that:

WHERE DATEDIFF(month, CAST(t.Ticket_OpenDate as date), GETDATE()) = 1

See db<>fiddle for an example.

Upvotes: 1

Related Questions