Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: Need help converting the following PL SQL code to T SQL code

    Hi , Can someone please help me convert the PL SQL code below into a MSSQL code? Thanks in advance.

    create or replace
    FUNCTION JULIAN_TO_DATE ("JULIAN_DATE" IN NUMBER, "TIME_OF_DAY" IN NUMBER DEFAULT null) RETURN DATE DETERMINISTIC IS
    --initialize variables here
    l_date_ch varchar2(6) := null;
    l_date_time varchar2(13) := null;

    l_century number := 0;
    l_year number := 0;
    l_days number := 0;
    l_time_hours number := 0;
    l_time_minutes number := 0;
    l_time_seconds number := 0;

    -- main body
    BEGIN

    IF julian_date = 0 or julian_date is null
    THEN
    return null;
    END IF;

    l_date_ch := lpad(nvl(to_char(julian_date), '0'), 6, '0');

    IF substr(l_date_ch, 1, 1) = '1'
    THEN

    l_century := 20;

    ELSIF substr(l_date_ch, 1, 1) = '0'
    THEN

    l_century := 19;

    ELSE
    l_century := 21;

    END IF;

    l_year := to_number(substr(l_date_ch, 2, 2));

    l_days := to_number(substr(l_date_ch, 4, 3));

    l_time_hours := to_number(substr(lpad(nvl(to_char(time_of_day), '0'), 6, '0'), 1, 2));
    l_time_minutes := to_number(substr(lpad(nvl(to_char(time_of_day), '0'), 6, '0'), 3, 2));
    l_time_seconds := to_number(substr(lpad(nvl(to_char(time_of_day), '0'), 6, '0'), 5, 2));

    -- Check values of the time parameter and re-set as necessary. JDE sends crap sometimes.

    IF l_time_seconds >= 60
    THEN

    l_time_seconds := mod(l_time_seconds, 60);
    l_time_minutes := l_time_minutes + 1; -- increment the minute if over 60 seconds

    END IF;

    IF l_time_minutes >= 60
    THEN

    l_time_minutes := mod(l_time_minutes, 60);
    l_time_hours := l_time_hours + 1; -- increment the hour if over 60 minutes

    END IF;

    IF l_time_hours >= 24
    THEN

    l_time_hours := 0;
    l_days := l_days + 1; -- increment the days if over 24 hours

    END IF;

    IF l_days > 366
    THEN

    l_days := '001';
    l_year := l_year + 1;

    ELSIF (l_days > 365) and (mod(l_year, 4) > 0)
    THEN

    l_days := 001;
    l_year := l_year + 1;

    END IF;

    l_date_time := to_char(l_century) ||
    lpad(to_char(l_year), 2, '0') ||
    lpad(to_char(l_days), 3, '0') ||
    lpad(to_char(l_time_hours), 2, '0') ||
    lpad(to_char(l_time_minutes), 2, '0') ||
    lpad(to_char(l_time_seconds), 2, '0');

    return to_date(l_date_time, 'rrrrdddhh24miss');

    EXCEPTION
    WHEN OTHERS THEN
    NULL; -- enter any exception code here
    RETURN NULL;
    END;

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by manaXmizery View Post
    Hi , Can someone please help me convert the PL SQL code below into a MSSQL code?
    If you want a stored procedure for Microsoft SQL Server then why did you post in the MySQL section?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    CREATE FUNCTION dbo.JULIAN_TO_DATE (@julian_date INT, 
    					@time_of_day INT = null) 
    RETURNS DATETIME
    AS
    BEGIN
    	IF COALESCE(@julian_date, 0) = 0
    		RETURN NULL;
    		
    	RETURN DATEADD(YEAR, @julian_date / 1000 - 1900, @julian_date % 1000 - 1) + 
    		CONVERT(TIME,
    			RIGHT('0' + CAST(COALESCE(@time_of_day, 0) / 10000 AS VARCHAR(2)), 2) + ':' +
    			RIGHT('0' + CAST((COALESCE(@time_of_day, 0) % 10000) / 100 AS VARCHAR(2)), 2) + ':' +
    			RIGHT('0' + CAST(COALESCE(@time_of_day, 0) % 100 AS VARCHAR(2)), 2),
    			114)
    END;
    GO
    
    SELECT dbo.JULIAN_TO_DATE (2011099, 150203) 
    SELECT dbo.JULIAN_TO_DATE (2011099, NULL) 
    SELECT dbo.JULIAN_TO_DATE (NULL, 150203)
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    If you want to convert the Astronomer's Julian date to a CE date, do it with a calendar table in any SQL.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •