Selecting for previous month (December 2022) in January 2023

Question

Question

Jared Severson · Jan 10, 2023

Hello!

I'm new to the system I'm working on, and I have a part of a statement that needs to grab the previous month.

....and MONTH(TX3.date_of_service)= MONTH(dateadd(mm,-1,GETDATE()))

However, when it hits January, I'm left with month 0.

Does anyone know a workaround to grab December of the previous year, while still functioning normally for the rest of the current year.

Thank you!

Product version: Caché 2018.1

Discussion (4)0

Log in or sign up to continue

David Hockenbroch · Jan 10, 2023

Are you sure that's where your problem is? If I do:

select MONTH(dateadd(mm,-1,GETDATE()))

I get 12. If I create a query with a where clause similar to yours on my data, it works as expected.

0 0

score 0 · Answer 1 · 2023-01-10T11:50:34-05:00

You're correct! I guess I'm back to square one. The query runs inside of the NetSmart Avatar system and pulls no data for December. hmmm

score 0 · Answer 2 · 2023-01-10T12:20:25-05:00

The problem is in the line above, I believe:

and (YEAR(TX3.date_of_service)= YEAR(GetDate()))
and MONTH(TX3.date_of_service)= MONTH(dateadd(mm,-1,GETDATE()))

score 0 · Answer 3 · 2023-01-10T12:37:23-05:00

Jared Severson · Jan 10, 2023

Never mind. I figured it out. Thank you!

0 0