Results 1 to 6 of 6

Thread: Use SQL to Convert Current Date to E1 Julian Date

  1. #1

    Use SQL to Convert Current Date to E1 Julian Date

    SQL Server:
    Code:
    SELECT (DATEPART(year, GETDATE())-1900)*1000 + DATEPART(dayofyear, GETDATE())
    Last edited by brother_of_karamazov; 04-03-2018 at 07:32 AM.

  2. #2
    New Member
    Join Date
    Aug 2011
    Location
    Czech Rep.
    Posts
    37
    I use this for Oracle DB: (select (to_char ( sysdate,'YYYY')-1900)*1000 +to_char ( sysdate,'DDD') from dual)
    Last edited by xxpeta; 04-03-2018 at 08:52 AM.
    E1 E8.12, E9.20

  3. #3
    Quote Originally Posted by xxpeta View Post
    I use this: (select (to_char ( sysdate,'YYYY')-1900)*1000 +to_char ( sysdate,'DDD') from dual)
    Oracle database? If so, please label it as such.

  4. #4
    New Member
    Join Date
    Mar 2002
    Location
    New Zealand
    Posts
    46
    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
    EnterpriseOne 9.1 Tools 9.1.5.2, Oracle 11g Solaris 5.11

  5. #5
    New Member
    Join Date
    Jan 2017
    Location
    Toronto, ON
    Posts
    6
    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 ?

  6. #6
    Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    63
    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.
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.