altquark
Legendary Poster
I know that there are some scripts out there to convert Julian dates on Oracle - theres even a couple of functions for SQL Server - but I couldn't find them and went through and created my own. They are probably not as efficient - but the code is a lot shorter and neither uses a function.
Heres the julian conversion :
Converting todays date to a julian date :
SELECT (((DATEPART(year, GETDATE()) +100) - 2000)*1000)+DATEDIFF(day,
cast(DATEPART(year, GETDATE()) as char(4))+'-01-01'
, getdate())
converting the julian date back to a SQL Server date (using the highest GLUPMJ in F0911 as an example):
select top 1 glupmj,
(cast(substring(cast(glupmj as char(6)),1,len(glupmj)-3) as int)-100)+2000 as nyear
,dateadd(day,cast(right(glupmj,3)as int),cast((cast(substring(cast(glupmj as char(6)),1,len(glupmj)-3) as int)-100)+2000 as char(4))+'-01-01') as ndate
from jde_crp.crpdta.f0911
I think the latter function could be cleaned up a bit - but it does certainly work.
If this works for everyone, could we see this in tips & tricks
Heres the julian conversion :
Converting todays date to a julian date :
SELECT (((DATEPART(year, GETDATE()) +100) - 2000)*1000)+DATEDIFF(day,
cast(DATEPART(year, GETDATE()) as char(4))+'-01-01'
, getdate())
converting the julian date back to a SQL Server date (using the highest GLUPMJ in F0911 as an example):
select top 1 glupmj,
(cast(substring(cast(glupmj as char(6)),1,len(glupmj)-3) as int)-100)+2000 as nyear
,dateadd(day,cast(right(glupmj,3)as int),cast((cast(substring(cast(glupmj as char(6)),1,len(glupmj)-3) as int)-100)+2000 as char(4))+'-01-01') as ndate
from jde_crp.crpdta.f0911
I think the latter function could be cleaned up a bit - but it does certainly work.
If this works for everyone, could we see this in tips & tricks