Convert Date to Julian Date

  • I need to convert a date type (2017-11-27 for example) to a Julian date.

    I have seen several examples where the conversion goes in reverse....or solutions that do not match the Navy's conversion site

    Maybe they are wrong? I do not know...but I will take any help I can get.


  • This is what i have for converting to a Julian date, also included is converting a Julian date back to a regular date.

    DECLARE @input_date DATETIME;

    SELECT @input_date = getdate();
    -- an example of what is returned
    SELECT datepart(year, @input_date) * 1000 + datepart(dy, @input_date) AS Julian_Date, @input_date AS input_date;

    DECLARE @jul_date INT;
    -- sets the variable with the date
    SET @jul_date = (SELECT datepart(year, @input_date) * 1000 + datepart(dy, @input_date));

    SELECT @jul_date AS jul_date;
    -- converts back to a regular date
    SELECT DATEADD(dd, CAST(RIGHT(@jul_date, 3) AS INT) - 1, CAST(CONCAT('01/01/', LEFT(@jul_date, 4)) AS DATETIME)) AS in_date

    Values returned:
    Julian_Date    input_date
    2017331    2017-11-27 15:48:25.257


    2017-11-27 00:00:00.000

    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • There are two interpretations for "Julian Date"

    The one you've got is the corrupted version - being the year followed by the day number within the year.

    The real / original Julian Date (and the one supported by the date converter you linked) is the number of days since 1 January 4713BC; just for fun, it's a 7980 year cycle, so there's only 1200 or so years for you to get the calculation sorted...

    Thomas Rushton

  • SQL Server dates can be cast as decimals, with the integer portion as days since 1900-01-01 00:00. The Julian date is also a decimal value of days since a base date. The Julian date for 1900-01-01 00:00 is 2415020.5. Adding this value to the decimal representation of a SQL datetime gives the Julian date. The SQLServer datetime should be translated to UTC before decimal conversion.

    To get the current Julian date, an expression like this works:

    SELECT CAST(CAST(GETUTCDATE() as datetime) AS DECIMAL(18,8)) + 2415020.5 [JulianDate]

  • Age Calculation

    Code for TallyGenerator

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply