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?

YearMonth

date

DATEADD(month,DATEDIFF(month,'20010201',GETDATE()),'20010101')

4 Answers
4

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)


GETDATE()

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

1900-01-01

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


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:

YearMonth

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.

GETDATE()

DATEADD

DATEDIFF

EOMONTH

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:

eomonth()

month()

year()

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


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.