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!
Quote from: gatorfe on 04 Feb 2015 02:31:51 PM
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.
thank you for the suggestions!