Convert Date to Julian Date

• SQL Espo

  SSCrazy

  Points: 2678

  More actions

  November 27, 2017 at 2:32 pm

  #326518

  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

  http://aa.usno.navy.mil/data/docs/JulianDate.php

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

  Thanks!
  Michael

• below86

  SSChampion

  Points: 11562

  More actions

  November 27, 2017 at 2:55 pm

  #1969617

  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

  jul_date
  2017331

  in_date
  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.

• Thomas Rushton

  SSC-Insane

  Points: 22649

  More actions

  November 28, 2017 at 3:40 am

  #1969654

  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
  blog: https://thelonedba.wordpress.com

• tbailey 19088

  SSCrazy

  Points: 2317

  More actions

  November 29, 2017 at 9:48 am

  #1969942

  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]

• Sergiy

  SSC Guru

  Points: 110209

  More actions

  November 30, 2017 at 2:38 am

  #1970003

  Age Calculation
  

  _____________
  Code for TallyGenerator