Convert Julian Date to Normal Date in Node.js for IBM DB2 iSeries

Question

In one of my application development I am using IBM DB2 iSeries database connected with DataDirect technology given by Progress with db2.jar. In database the date stored as 7 digit Julian format that I have to convert in normal date as timestamp in Node.js as my data layer has been implemented in Node.js that has to deploy on Apigee. I need a generic solution that can convert this julian date to normal date at SQL query level as well as in Node.js

Example of Julian date is 1160920 this is corresponds to 2016-09-20.

Did you try to google it first? There's many answers here on StackOverflow, just use search. — Molda, Nov 22 '16 at 10:34
Whatever you mean by "Julian date", 1160920 cannot possibly correspond to 20 Sept. 2016 in the Gregorian calendar. — mustaccio, Nov 22 '16 at 13:10
That's not a Julian date, it's a regular date stored with only 7 digits as CYYMMDD. — Charles, Nov 22 '16 at 13:58

score 4 · Answer 1 · answered Nov 23 '16 at 05:30

After much investigation I come with a solution for both at SQL level as well as node.js level.

To convert 7 digit Julian date into Normal Date using SQL query you can use below query for IBM DB2 iSeries database -

SELECT DATE(((1160920 /10000)+1900)|| '-' ||
(MOD(1160920 ,10000) /100)|| '-' ||
MOD(1160920 ,100))
FROM
SYSIBM.SYSDUMMY1

To perform same conversion using node.js use below code it will convert perfectly

var dateFormat = require('dateformat');
function julianIntToDate(n) {

    var yy = ((n/10000)+1900);
    var year = Math.floor(yy);
    var mm = ((n % 10000)/100);
    var mon = Math.floor(mm);
    mon = leadingZero(mon);
    var dd = (n % 100);
    dd = leadingZero(dd);
    console.log("Year--"+year);
    console.log("Month--"+mon);
    console.log("Day--"+dd);

    var now = new Date(year+"-"+mon+"-"+dd);
    console.log("Converted Date-->"+now);
    orderDate = dateFormat(now, "yyyy-mm-dd'T'HH:mm:ss-0500");
    return (orderDate);

    function leadingZero(value) {
    if (value < 10) {
    return "0" + value.toString();
    }
    return value.toString();
    }
}
console.log("Julian Date--->"+1160920);
console.log("Normal Date--->"+julianIntToDate(1160920).toString());

Convert Julian Date to Normal Date in Node.js for IBM DB2 iSeries

1 Answers1