How to convert long Julian to YYYYMMDD format using SQL
In
the system that we have in our office, all the date values are in long Julian format.
When we are working with SQL it is a headache to convert those long Julian
dates to YYYYMMDD numeric format. So I have decided to create two SQL functions.
One is for convert 7-character long Julian date into YYYYMMDD numeric value and
other one is for convert 7-Digits long Julian date into YYYYMMDD numeric value.
In my previous post I showed how to create a SQL scalar function, this time I
am going to create SQL scalar function and external scalar function both.
First will see how to achieve above task using an external scalar function.
1st
Step
Let’s create RPGLE module. This is a NOMAIN
module. In that module I have created two procedures. One procedure accepts a
7-char parameter and returns 8-digits value and the other one accepts a
7-digits parameter and returns 8-digits value.
*-------------------------------------------------------------------
ctl-opt nomain;
*-------------------------------------------------------------------
/////////////////////////////////////////////////////////////
// Prototype definitions //
/////////////////////////////////////////////////////////////
//Prototype for julToNum
dcl-pr julToNum zoned(8);
*n zoned(7) const;
end-pr;
//Prototype for jchToNum
dcl-pr jchToNum zoned(8);
*n char(7) const;
end-pr;
/////////////////////////////////////////////////////////////
// This Procedure converts numeric long
julian date //
// into ISO numeric date. //
/////////////////////////////////////////////////////////////
dcl-proc julToNum export;
//Prototype interface for incoming
parms
dcl-pi *n zoned(8);
julDat zoned(7) const;
end-pi;
monitor;
return
%dec(%char(%date(julDat:*longjul):*iso0):8:0);
on-error;
return 0;
endmon;
end-proc julToNum;
/////////////////////////////////////////////////////////////
// This Procedure converts character
long julian date //
// into ISO numeric date. //
/////////////////////////////////////////////////////////////
dcl-proc jchToNum export;
//Prototype interface for incoming
parms
dcl-pi *n zoned(8);
julDat char(7) const;
end-pi;
monitor;
return
%dec(%char(%date(%int(julDat):*longjul):*iso0):8:0);
on-error;
return 0;
endmon;
end-proc jchToNum;
|
2nd
Step
Compile
RPGLE module.
