Getting Last 30 days from Today

[gatorfe]

In the following code we are trying to get the last 30 days from today of the HHJDAT instead of just looking at a specific month period.  This is for DM 10.2.1.  We are running against an ERP LX system.

A.HHCURR as CURRENCY_CODE,
A.HHRATE as EXCHANGE_RATE,
B.LHJNML as MODEL_LINE,
C.CRDESC as ACCOUNT_DESCRIPTION

FROM   ERPLXF.GHH A join ERPLXF.GLH B on A.HHLDGR = B.LHLDGR   
            join ERPLXF.GCR C on C.CRIAN = B.LHIAN
            left outer join ERPLXF.GLA D on D.LALDGR = B.LHLDGR

            where
            CAST(A."HHJDAT" AS VARCHAR( 8 ) ) >= '20150101'
             and
            CAST(A."HHJDAT" AS VARCHAR( 8 ) ) <= '20150131'

We tried these options replacing the where clause above but they did not work,
CAST(A."HHJDAT" AS VARCHAR(8 ) ) >= DATEADD(DAY,-30,GETDATE())
CAST(A."HHJDAT" AS VARCHAR(8 ) ) >  DATEADD(dd,-30,GETDATE())

Any suggestions would be greatly appreciated.  Thank you!

[MFGF]

In the following code we are trying to get the last 30 days from today of the HHJDAT instead of just looking at a specific month period.  This is for DM 10.2.1.  We are running against an ERP LX system.

A.HHCURR as CURRENCY_CODE,
A.HHRATE as EXCHANGE_RATE,
B.LHJNML as MODEL_LINE,
C.CRDESC as ACCOUNT_DESCRIPTION

FROM   ERPLXF.GHH A join ERPLXF.GLH B on A.HHLDGR = B.LHLDGR   
            join ERPLXF.GCR C on C.CRIAN = B.LHIAN
            left outer join ERPLXF.GLA D on D.LALDGR = B.LHLDGR

            where
            CAST(A."HHJDAT" AS VARCHAR( 8 ) ) >= '20150101'
             and
            CAST(A."HHJDAT" AS VARCHAR( 8 ) ) <= '20150131'

We tried these options replacing the where clause above but they did not work,
CAST(A."HHJDAT" AS VARCHAR(8 ) ) >= DATEADD(DAY,-30,GETDATE())
CAST(A."HHJDAT" AS VARCHAR(8 ) ) >  DATEADD(dd,-30,GETDATE())

Any suggestions would be greatly appreciated.  Thank you!

Hi,

Your original predicate was comparing against manually specified string values ('20150101' and '20150131') so you used a CAST function to convert the HHJDAT values to varchar(8 ) in order for them to be the same data type. Correct?

I'm assuming the DATEADD function will return a date not a character string? If so, you're not going to be able to compare a varchar(8 ) string with a date. As I see it you have two choices:

A."HHJDAT"  >= DATEADD(DAY,-30,GETDATE()) and A."HHJDAT" <= GETDATE()

or

CAST(A."HHJDAT" AS VARCHAR(8 )) >= CAST(DATEADD(DAY,-30,GETDATE()) AS VARCHAR(8 )) and CAST(A."HHJDAT" AS VARCHAR(8 )) <= CAST(GETDATE() AS VARCHAR(8 ))

I assume the former will be more efficient if the data types of HHJDAT and GETDATE() are the same.

Cheers!

MF.

[gatorfe]

thank you for the suggestions!