November 27, 2017 at 2:32 pm
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
November 27, 2017 at 2:55 pm
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.
November 28, 2017 at 3:40 am
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
November 29, 2017 at 9:48 am
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]
November 30, 2017 at 2:38 am
_____________
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