A7.3 Need DB2 SQL to convert YYYYMMDD to Julian date

[GodonJones]

I am using DB2 SQL on iseries as/400 in JDE World 7.3. I am working to convert dates to julian dates for out accounting monthend processing. The DBU table DATET has some of the dates but not all of them are in julian format. I need SQL statements to convert YYYYMMDD dates to Julian JDE date format.

 

[DaveWagoner]

Here's a template I use, I've been able to do just about everything I've needed with these over the years. I found the original set somewhere on stackoverflow or ibm:

/*
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))
*/

 

[slandess]

SELECT INT(TO_CHAR('2019-10-18','YYYYDDD'))-1900000 as JDE_JULIAN FROM sysibm.sysdummy1

----------------------------
....+....1....
JDE_JULIAN
119,291
******** End of data ********

 

[eydeak]

I think I got the following off jdelist in the past. DB2 SQL that demonstrates a lot of ways to do the data conversions, formatting, etc. Reposting it in case I did find it someplace else:

/* Simple method to convert the JDE dates to a readable date. */

select SDTRDJ, date(char(SDTRDJ+1900000)) as FormattedDate
from testdta.f42119
where SDDOCO = 16559822;

/* Example showing ways to deal with date and time */

select sddoco,
sddcto,
sdlnid,
sdupmj,
sdtday,
right('000000'||sdtday,6) as wholetime,
substr(right('000000'||sdtday,6),1,2)||'.'||substr (right('000000'||sdtday,6),3,2)||'.'||substr(right ('000000'||sdtday,6),5,2) as propertime,
sdupmj,
1900000+sdupmj as DB2JULDATE,
date(cast((1900000+sdupmj) as char(7))) as strdate,
timestamp(cast(date(cast((1900000+sdupmj) as char(7))) as char(10))||'-'||cast(substr(right('000000'||sdtday,6),1,2)||'.' ||substr(right('000000'||sdtday,6),3,2)||'.'||substr(right('000000'||sdtday,6),5,2) as char(8))) as DB2TimeStamp,
year(current date) * 1000 + dayofyear(current date) -1900000 as JDEJulianToday,
year(current date - 1 day) * 1000 + dayofyear(current date - 1 day) -1900000 as JDEJulianYesterday,
year(current date) * 1000 + dayofyear(current date) - 2000000 as IBMShortJulian,
year(current date) * 1000 + dayofyear(current date) as IBMJulian,
left(char(current time),2) || substr(char(current time),4,2) || substr(char(current time),7,2) as JDETime,
current time as SystemTime,
session_user as WhoAmI,
month(date(cast((1900000+sdupmj) as char(7)))) as MonthValue,
year(date(cast((1900000+sdupmj) as char(7)))) as YearValue,
dayofweek(date(cast((1900000+sdupmj) as char(7)))) as DayOfWeekValue,
dayname(date(cast((1900000+sdupmj) as char(7)))) as DayNameValue
from PRODdta.f4211
fetch first 1 rows only;

 

[slandess]

Ellen - you didn't answer the question which was asked.

"I am using DB2 SQL on iseries as/400 in JDE World 7.3. I am working to convert dates to julian dates for out accounting monthend processing. The DBU table DATET has some of the dates but not all of them are in julian format. I need SQL statements to convert YYYYMMDD dates to Julian JDE date format."

 

[GodonJones]

If I run - select fmthe, date(char(fmthe+1900000)) as FormattedDate from datet - I get the result set of


CURRENT FISC MTH END FORMATTEDDATE
20221030 ++++++++

 

[cazzafed]

Aquí hay una plantilla que uso, he podido hacer casi todo lo que he necesitado con estos a lo largo de los años. Encontré el conjunto original en algún lugar de stackoverflow o ibm:

[CÓDIGO=sql]/*
Conversión del campo de fecha AS/400 (inglés) a JD Edwards Julian:
(AÑO(campo de fecha)-1900)*1000 + DÍA DEL AÑO(campo de fecha)


Conversión de JD Edwards Julian a campo de fecha AS/400 (inglés):
(FECHA(dígitos(DECIMAL(JDEDateField +1900000,7,0))))


Cálculo: Obtener la fecha juliana de JD Edwards para la fecha de hoy (relativa):
((AÑO(MARCA DE TIEMPO ACTUAL)-1900)*1000 + DÍA DEL AÑO(MARCA DE TIEMPO ACTUAL))


Obtener la fecha juliana de JD Edwards durante 30 días antes de la fecha de hoy (relativa):
((AÑO(MARCA DE TIEMPO ACTUAL - 30 DÍAS)-1900)*1000 + DÍA DEL AÑO(MARCA DE TIEMPO ACTUAL - 30 DÍAS))
*/[/CÓDIGO]

Hi @DaveWagoner
in case of my julian date is = 0, how i execute the formate?
i write this question why i try format this julian date = 0, but obtain te error 'day between 1 and 365 '

 

[DaveWagoner]

Hi @DaveWagoner
in case of my julian date is = 0, how i execute the formate?
i write this question why i try format this julian date = 0, but obtain te error 'day between 1 and 365 '

if a 0 is a possible value then you'll need to handle that with something like a case statement

case when JDEDateField >0
then (DATE(digits(DECIMAL(JDEDateField +1900000,7,0))))
else 'no date'
end

 

[cazzafed]

if a 0 is a possible value then you'll need to handle that with something like a case statement

case when JDEDateField >0
then (DATE(digits(DECIMAL(JDEDateField +1900000,7,0))))
else 'no date'
end

Hi, i try this SQL, but i receive 'inconsistent datatypes: expected DATE got CHAR' error.

i obtain correct functionally with this sql; 'case when was trx > 0
then to_char(to_date(1900000+wastrx, 'YYYYDDD'))
else replace(wastrx, 0, null)'.

Thank you so much @DaveWagoner. I hope this SQL works in BIP...