Use SQL to Convert Current Date to E1 Julian Date

  • Thread starter brother_of_karamazov
  • Start date

brother_of_karamazov

Legendary Poster
SQL Server:
Code:
SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())
 
Last edited:
I use this for Oracle DB: (select (to_char ( sysdate,'YYYY')-1900)*1000 +to_char ( sysdate,'DDD') from dual)
 
Last edited:
MS SQL Function to convert DateTime into JDE Julian

--CREATE FUNCTION dbo.JDEJulian (@inputdate datetime)
--RETURNS int
--
--BEGIN
-- declare @yr varchar(4)
-- declare @yeartest varchar(30)
-- declare @julianday int
-- declare @julian int
--
-- set @yr = (year(@inputdate))
-- set @yeartest = '01-01-'+@yr
-- set @julianday = (Datediff(day,@yeartest,@inputdate))+1
-- set @julian = '100000'+(1000*(right(@yr,2)))+@julianday
-- RETURN(@julian)
--END

MS SQL Function to convert JDE Julian into DateTime

--CREATE FUNCTION dbo.JDEDate (@inputdate varchar(6))
--RETURNS datetime
--
--BEGIN
-- declare @yr varchar(4)
-- declare @yeartest datetime
-- declare @julianday int
-- declare @output datetime
--
-- set @yr = (substring((@inputdate),2,2))
-- set @yeartest = '01-01-'+@yr
-- set @julianday = (right(@inputdate,3))
-- set @output = CONVERT ( VARCHAR (10), DATEADD( day , @julianday -1, @yeartest ) , 120 )
--
-- RETURN(@output)
--END
 
will that convert year less than 2000 ?

i tried that for date 1995-08-25 and returned only 5 digits 95237 it should return 095237

am i doing something wrong ?
 
Hi Ahmad, 95237 is correct since JDEDATE is stored in the database as a number, not a character string. What might be confusing is that xxpeta's example above would generate the character string '95237' instead of a number 95237.
 
Back
Top