CREATE OR REPLACE function PRODDTA.jde_date_to_char
( jde_date number,
format_str varchar2 := 'DD/MM/YYYY'
)
return varchar2
is
begin
/* Function to convert dates in JDE to format specified */
return
to_char( jde_date_to_date(jde_date), format_str );
end jde_date_to_char;
CREATE OR REPLACE function PRODDTA.jde_date_to_date
( jde_date number
)
return date
is
begin
/* Function to convert dates in JDE to oracle dates
JDE stores it dates in a 6 digit number field
1st 3 characters is the number of years since 1900
next 3 characters is the day # in the year */
if nvl(jde_date,0) = 0
then
return null;
else
return
to_date(
to_char(
to_number(
substr(to_char(jde_date,'000000'),2,3)
)+1900
)||
substr(to_char(jde_date,'000000'),5,3),
'YYYYDDD'
);
end if;
end jde_date_to_date;
1 comment:
If you want to do the same on MS SQL
---- Create function
create function dbo.Julian_date_Conv (@indate int)
returns datetime
as
begin
declare @date datetime
if @indate < 101001
set @date=NULL
else
set @date=dateadd(day, cast(right(@indate,3) as int), cast(cast(left(@indate, 3)+1900 as varchar(4))+'-01-01' as datetime))
return(@date)
end
e.g.
select 105251, dbo.Julian_date_Conv(105251)
Post a Comment