Converting Julian Dates in iSeries Query and year 2040

chemker

Well Known Member
In iSeries query we've used the same method to convert julian dates to gregorian for years. Here's the calc in Define Result Fields:

Field Experession Len Dec
D1 juldate+1900000 7 0

DATE DATE(DIGITS(D1))

And this has worked great until we used a date in the year 2040. Now it seems the DATE expression runs into an issue, but I have not heard of any IBM issues with this.

Has anyone else seen this issue?

Thank you.
 
[ QUOTE ]
In iSeries query we've used the same method to convert julian dates to gregorian for years. Here's the calc in Define Result Fields:

Field Experession Len Dec
D1 juldate+1900000 7 0

DATE DATE(DIGITS(D1))

And this has worked great until we used a date in the year 2040. Now it seems the DATE expression runs into an issue, but I have not heard of any IBM issues with this.

Has anyone else seen this issue?

Thank you.

[/ QUOTE ]

It sounds like you have run into the date conversion "window".

Apparently the DATE function uses only a 2-digit year to perform the date conversion, and can only handle dates between 1940 and 2039.


Try this:

Field Experession Len Dec
D1 juldate+1900000 7 0

DATE CHAR(DIGITS(D1),USA)

Unfortunately, the CHAR function is going to give you a date which is formatted as MM/DD/YYYY instead of MM/DD/YY, so if you need the MM/DD/YY format you may have to define another field and substring out what you need from this one.

Regards,
 
Does anyone know how to extract the month from a Julian date? I have invoice dates which I'm trying summarize by month vs retrieving every record for a given month.
 
Here's a thought...you could create a physcial file stored in a common library that contains multiple pieces of information for each date within a 100 year span. Then just join to the new "date file" to retrieve one or more values.

We have a file that ranges from 1950 to 2050 and has 36,527 entries. It contains the following elements:

Julian Date
Date in MDY Format
Date in YMD Format
Julian Edited
MDY Edited
YMD Edited
Day of Week
Name of Day
Name of Month
Julian Day Number

Create a couple of indexes and everyone can use this for multiple date conversions with iSeries query.
 
There is a date conversion server program in JDE world(I forgot the name)
which will convert from Julian to MDY format and then you can extract
month from that.

Thank you

Vijay Shetty
Rug Doctor Inc
PH: (972) 673-1530
FAX: (972) 673 1424



From: worton <[email protected]>
To: [email protected],
Date: 05/01/2014 12:52 PM
Subject: Re: Converting Julian Dates in iSeries Query and year
2040
Sent by: <[email protected]>



Does anyone know how to extract the month from a Julian date? I have
invoice dates which I'm trying summarize by month vs retrieving every
record for a given month.
 
Well since you are in the JDE List, I have to assume you have F00365 and you could join to it using the Julian date and it will have the individual fields for Month, Day, and Year. And maybe you will probably want to summarize by month within a given year.

Also in this forum from several years back (11/13/2003) was a post by Mike Iaconis (miaconis) that I have used at several of my employees. Here is a link to that thread:



Hope it helps but of course it is a function for SQL but you can wrap this with the Month, Year, or add x Days or y Months as it converts the Julian Date to a Date Field and is totaly awesome in SQL statements.
 
Here is my SQL Cheatlist of date conversions for DB2, JDE going both ways:

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 JDEJulian,
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
from yourlibraryhere/f4211
fetch first 1 rows only;
 
Back
Top