Converting the julian date....this time for SQL server users

[altquark]

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

 

[Zoltan_Gyimesi]

Hi Jon,
... a bit OFF
[ QUOTE ]
If this works for everyone, could we see this in tips & tricks

[/ QUOTE ]
... of course

Unfortunately I can not try it (no time, no sytem access, ...and no knowledge)
... but I hope (or I am sure) many of us will make a try.
Best regards,
Zoltán

 

[Ray Justus]

Another alternative. Just in case you have some missing dates.

SELECT
CASE WHEN YADT != 0 THEN CAST('1/1/' + CAST(1900 + FLOOR(YADT / 1000) AS CHAR) AS DATETIME) + CAST(YADT AS INT) % 1000 - 1 ELSE NULL END AS [Termination Date],
CASE WHEN YADT != 0 THEN MONTH(CAST('1/1/' + CAST(1900 + FLOOR(YADT / 1000) AS CHAR) AS DATETIME) + CAST(YADT AS INT) % 1000 - 1) ELSE 0 END AS [Month],
CASE WHEN YADT != 0 THEN DAY(CAST('1/1/' + CAST(1900 + FLOOR(YADT / 1000) AS CHAR) AS DATETIME) + CAST(YADT AS INT) % 1000 - 1) ELSE 0 END AS [Day],
CASE WHEN YADT != 0 THEN YEAR(CAST('1/1/' + CAST(1900 + FLOOR(YADT / 1000) AS CHAR) AS DATETIME) + CAST(YADT AS INT) % 1000 - 1) ELSE 0 END AS [Year]
FROM PRODDTA.F060116

 

[james_thurman]

Except that the first query to convert today’s date to a Julian is off by 1 day I believe...


Here are a couple for the ever so slightly shorter category:

Converting todays date to a julian date :
SELECT (DATEPART(year,GETDATE()) - 1900) * 1000 + DATEPART(dayofyear, GETDATE())



Converting the julian date back to a SQL Server date:
SELECT CONVERT(varchar(12),CONVERT(datetime, '01/01/'+ SUBSTRING(STR(glupmj,6),2,2))
+ (CONVERT(integer, SUBSTRING(STR(glupmj,6),4,3),104 )-1) ,101)
FROM jde_crp.crpdta.F0911


Or for just a quick calculator:
DECLARE @DATE integer
SELECT @DATE = 106172
SELECT CONVERT(varchar(12),CONVERT(datetime, '01/01/'+ SUBSTRING(STR(@DATE,6),2,2))
+ (CONVERT(integer, SUBSTRING(STR(@DATE,6),4,3),104 )-1) ,101)





James Thurman
Group Focus, Inc.
(210) 861-5142 – Cell
(630) 982-6414 – Fax
[email protected]

 

[altquark]

sorry - yes, I actually wanted "YESTERDAYS" date for the program I was using !

I should of said that. Just don't take off 1 day if you want todays date converted !!!!!