Converting Julian dates using SQL in DB2

agbeer

Member
Hi all,

Has anyone managed to convert julian dates to "real" dates in SQL using DB2?

There's several of us here who've done it any which way on Oracle databases, but can't get anything to work on DB2 short of exporting to Excel and converting there...

I'd appreciate a silver bullet if anyone's got one!

Cheers,

Andy.
 
This works when using the 'run an SQL script' from iNavigator:

REPLACE(CHAR(DATE(SUBSTR(DIGITS(abupmj+1900000),2,7)),iso),'-','')


-- Example: this is selecting the address book number & date updated from the F0101
select aban8, REPLACE(CHAR(DATE(SUBSTR(DIGITS(abupmj+1900000),2,7)),iso),'-','') from testdta.F0101
 
Hi Andy,

Here you go:

SELECT
ABUPMJ,
CAST(CAST( ((CAST( (ABUPMJ - MOD(ABUPMJ, 1000)) / 1000 AS INT) + 1900) * 1000) +
MOD(ABUPMJ, 1000) AS CHAR(7)) AS DATE) AS REALDATE
FROM TESTDTA.F0101

Should result in something like this:

ABUPMJ REALDATE
--------- -----------
112335 2012-11-30
112335 2012-11-30
112335 2012-11-30
112335 2012-11-30
112335 2012-11-30
 
When you say DB2 I am guessing you mean DB2 on the iSeries ?

You can use the below (I have only used this in the Ops Navigator SQL interface , have not tried it on the green screen (STRSQL))



select varchar(date(char(1900000+<JULIAN_FIELD_NAME>)),USA) FROM SCHEMA.TABLE


For example

select varchar(date(char(1900000+RLEFFDATE)),USA) as "Effective Date" , varchar(date(char(1900000+RLEXPIRDATE)),USA) as "Expiry Date" FROM
SY900.F95921 where <your conditions>
 
Thank you so much!
It worked using IBM i Access Data Transfer from Windows version.

I've changed it just a little bit, to get the Gregorian date in my Region (Brazil) format:
date(char(1900000+GLDGJ)) as DGJ
 
awesome, simple example from ice_cube210! perfect! thx! only change i made for my use was to go with ISO format.
 
Back
Top