Best practice to date+time

1

Hi there!!

We are working with JDE system and get two fields from the table F42199, SLUPMJ (Julian Date) and SLTDAY (time).
We need a timestamp date+time, something like CONCAT(SLUPMJ,SLTDAY) as EventEnd.
which is the best way?

we used this way, but we have some problems

–date_time_1
TO_TIMESTAMP(CONCAT(CONCAT(SUBSTR(SLUPMJ,2,5),’ '),SUBSTR(CONCAT(‘0’,CAST(SLTDAY AS VARCHAR)),LENGTH(CONCAT(‘0’,CAST(SLTDAY AS VARCHAR)))-5,6) ),‘YYDDD HH24MISS’,1) AS “date_time_1”

thanks in advance

Regards
AJM

2

Hi,

What kind of problem are you getting? Sharing a profile of the query that is giving you trouble would also help us understand what is happening.

Also what data types are the columns? SLUPMJ seems to be a string column and SLTDAY is a timestamp?

3

Hi @doron !

SLUPMJ is Julian Date (default of JDE) and SLTDAY (text, without zero to left)
i.e.
SLUPMJ = 18213 & SLTDAY = 91542

Thanks in advance

Regards
AJM