sql - Getting all data from last month

Question

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.

标签： sqlsql-serverdatetsql

Answer 1

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)