Date > Previous days date 1

[TitleistDBA]

I'm trying to run the following query. I'm only looking for data the mmlmdt greater than the previous days date.
I should only get back 49 rows. However when I run my query here I get back data from 2004 as well. We are running this on and AS400 db so I'm not sure sure if current date works?
Thanks

SELECT mmitno, mmstat, mmitds, mmitty, mmmabu, mmcfi1, mmpuun,
mppopn, mmlmdt
FROM mvxcjdttst.mitmas LEFT OUTER JOIN mvxcjdttst.mitpop
ON (mmitno = mpitno
and MPALWQ = 'UPC')
WHERE mmlmdt > (day(current date) - 1)

http://www.acushnetcompany.com

http://www.titleist.com

 

[BrianTyler]

Should you be using
day(current date) - 1
or
days(current date) - 1

The first gives the day of the month, and the second a relative day number from the base date.

Brian

 

[MarcLodge]

Hi,
For today's date:

(day(current date)-1) will return the result 16
days(current date)-1 will return the result 732021

If your column mmlmdt is a true DB2 date then I think you need:

WHERE mmlmdt > current date - 1 day

Hope this helps

Marc

 

[TitleistDBA]

Thanks Marclodge.
That wass what I needed.

http://www.acushnetcompany.com

http://www.titleist.com