Datediff Getting the Previous Month

DATEDIFF Getting the previous month

You can use DATEADD

eg.

SELECT DATEADD(month, -1, GETDATE())

Get the records of last month in SQL server

SELECT * 
FROM Member
WHERE DATEPART(m, date_created) = DATEPART(m, DATEADD(m, -1, getdate()))
AND DATEPART(yyyy, date_created) = DATEPART(yyyy, DATEADD(m, -1, getdate()))

You need to check the month and year.

SQL get previous month (in January too)

Try This

select CASE WHEN month(getdate())>1 THEN  month(getdate())-1   ELSE 12   END ,

CASE WHEN month(getdate())>1 THEN YEAR (getdate()) ELSE YEAR (getdate()) -1 END

Get the previous month number including year

Try with case when like below:

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE CompanyID = 1205
AND Month = (case when MONTH(GETDATE())-1=0 then 12 else MONTH(GETDATE())-1 end)  AND Year  = (case when MONTH(GETDATE())-1=0 then YEAR (GETDATE())-1 else YEAR (GETDATE()) end)

How is this SQL function getting the first day of the previous month?

Rewriting it like this:

DATEADD(month, DATEDIFF(month, '20100101', getdate()) - 1, '20100101')

probably makes it clearer.

There's some dodgy and obscure stuff going on.

This

DATEDIFF(month, -1, getdate())

counts the month boundaries crossed between two dates, the first one is

   cast(-1 as datetime) --1899-12-31 00:00:00.000

Then it subtracts 2 from that and add that many months to

 cast(0 as datetime) --1900-01-01 00:00:00.000

Most optimal way to get all records IN previous month

Your query is slow because when you do DATEDIFF(m, [DATE_COL], GETDATE()) it can not use any indexes on the [Date_Col].

Anyway you can use the following where clause, this will use indexes on the [SettlementDate] and hopefully it should perform a lot better than the DATEDIFF() function.

WHERE [SettlementDate] >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)  
AND [SettlementDate]  < DATEADD(DAY,1,DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1))

---

Related Topics