DATE FUNCTION TO CALCULATE THE PREVIOUS MONTH 2

[Ann28]

Any help is appreciated!

I need to select records with END_DATE of previous month.
How do I account for January ?

SELECT  DISTINCT  
HIGH.MEMBER_SSN  
,   HIGH.BENEFIT_PLAN 
,   HIGH.BEN_DATE AS BEGIN_DATE
,   HIGH.END_DATE  AS HIGH_END_DATE
,   STD.BENEFIT_PLAN
,   STD.BEN_DATE  AS STD_BEGIN_DATE
,   STD.END_DATE  AS STD_END_DATE 
FROM  ELG_SUMMARY  HIGH INNER JOIN ELG_SUMMARY STD ON STD.MEMBER_SSN =   HIGH.MEMBER_SSN
WHERE 
(
 MONTH ( DATE (  HIGH.BEN_DATE  ) )  = MONTH ('2005-07-01')  
AND  HIGH.BENEFIT_PLAN = 'DOHIGH'  
)
AND
(
  MONTH (DATE ( STD.END_DATE)  )   =   [COLOR=red][b]MONTH (DATE( '2005-07-01')  )  - 01 [/b][/color red]
AND  STD.BENEFIT_PLAN = 'DOSTAN'  
)

THANKS A LOT,
Ann

 

[rbod]

for december[2004] data :
select month(current date - 8 month) from sysibm.sysdummy1

for ex, if you are in January of 2005, to get December data:
select month(date('01/08/2005') - 1 month) from sysibm.sysdummy1

in your code :
MONTH(DATE(STD.END_DATE)) = MONTH(CURRENT DATE - 1 MONTH)

 

[Ann28]

THIS MIGHT WORK AS WELL?
MONTH ( STD.END_DATE) = MONTH ( '2005-07-01') - 01 AND YEAR ( STD.END_DATE ) = YEAR ('2005-07-01')

 

[BrianTyler]

Ann,

This should work for any date

current date - 1 month - day(current date) days + 1 day

Brian