CODEWITHSUNDEEP
sqlsql-serverdatetime
user3127554
user3127554

Reputation: 577

Get the previous month number including year

I have a table in which I get the coupons of a specific company. My coupon table is like:

Company_Coupon

I keep my months in the format of their number (1-12).

When I update the total coupons for the invoicing, I want to update the ones from the previous month.

I do this by the following query: 

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE CompanyID = 1205
AND Month = MONTH(GETDATE())-1 AND Year  = YEAR (GETDATE())

My query works but I noticed that this won't work in January 2019.

How can I update this query so that it will work in January 2019?

Upvotes: 0

Views: 1872

Answers (4)

Roger Wolf
Roger Wolf

Reputation: 7692

If you use SQL Server 2012 or later version, you can employ the eomonth() function that returns last day of a previous month for a given date. From it, you can extract both month() and year() parts and use them in your query:

UPDATE c SET Total_Coupons = @count
from dbo.Company_Coupon c
WHERE c.CompanyID = 1205
    AND c.Month = MONTH(eomonth(GETDATE()))
    AND c.Year  = YEAR(eomonth(GETDATE()));

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

As I said in a comment, I'd prefer to have a single YearMonth column with the correct data type for datetime work, but we can do something very similar here:

UPDATE Company_Coupon
SET Total_Coupons = @count
FROM Company_Coupon
CROSS APPLY (SELECT 
  DATEADD(month,DATEDIFF(month,'20010201',GETDATE()),'20010101')) t(LastMonth)
WHERE CompanyID = 1205
AND Month = MONTH(LastMonth) AND Year  = YEAR(LastMonth)

The two dates used in the above expression do not matter much. All that matters really is the relationship between them. Here, the second date falls a month before the first and it's the relationship that effectively gets applied to GETDATE() by the DATEADD/DATEDIFF expression. It's a pattern that can be used in lots of different ways - e.g. a variant of this pattern can be used to find the last day of 3 months ago if you're not on a SQL version that supports EOMONTH.

Upvotes: 1

D-Shih
D-Shih

Reputation: 46219

You can try to get different number between 1900-01-01 and your data, then do some calculation to get last month.

Query 1:

SELECT DATEADD(month, DATEDIFF(month,0,'2019-01-01') - 1, 0)
UNION ALL
SELECT DATEADD(month, DATEDIFF(month,0,'2018-08-01') - 1, 0)

Results:

|                      |
|----------------------|
| 2018-12-01T00:00:00Z |
| 2018-07-01T00:00:00Z |

so you query can be

UPDATE Company_Coupon
SET Total_Coupons = @count
WHERE 
    CompanyID = 1205
AND 
    Month = MONTH(DATEADD(month, DATEDIFF(month,0,GETDATE()) - 1, 0))
AND 
    Year  = YEAR (DATEADD(month, DATEDIFF(month,0,GETDATE()) - 1, 0))

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

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)

Upvotes: 2

Related Questions