E9.2 Report Today minus 1 day

[SirInsky]

Hello everyone,

need help with this query.

SELECT ABALPH AS Kunde, SDLITM AS Artikel, SDDSC1 AS Beschreibung, SDSOQS AS Menge, date(digits(decimal(SDIVD+1900000,7,0))) AS Invoice, decimal(SDUPRC/10000,15,2) AS Einzelpreis, decimal(SDAEXP/100,15,2) AS Gesamtpreis,
SDDOCO AS Dokument, AIAC01 AS Region

FROM ALCATRAZ.PRODDTA.F42119 F42119, ALCATRAZ.PRODDTA.F0101 F0101, ALCATRAZ.PRODDTA.F03012 F03012


Now my question is , how can I get the today date minus 1 day ?

Thank you so much

 

[DaveWagoner]

Looks to me like you're writing AS400 SQL, in which case I can help.

((YEAR(CURRENT TIMESTAMP - 1 DAYS)-1900)*1000 + DAYOFYEAR(CURRENT TIMESTAMP - 1 DAYS))

Here's my entire AS400 SQL Date Snippet if you want to steal, I built this up from resources on ibm and proibably stackoverflow many years ago

/*
Converting AS/400 date field (english) to JD Edwards Julian:
(YEAR(DateField)-1900)*1000 + DAYOFYEAR(DateField)

Converting JD Edwards Julian to AS/400 date field (english):
(DATE(digits(DECIMAL(JDEDateField+1900000,7,0))))

Calculation:
Getting JD Edwards Julian date for today's date (relative):
((YEAR(CURRENT TIMESTAMP)-1900)*1000 + DAYOFYEAR(CURRENT TIMESTAMP))
 
Getting JD Edwards Julian date for 30 days prior to today's date (relative):
((YEAR(CURRENT TIMESTAMP - 30 DAYS)-1900)*1000 + DAYOFYEAR(CURRENT TIMESTAMP - 30 DAYS))
*/

 

[SirInsky]

date(digits(decimal(SDIVD+1900000,7,0)))

Thanks for your help, the data behind it are not linked to it. Now I have both dates