Convert a Date to Julian Date and then Store in a Numeric Field in SQL Server 2005

Question

I have a date field in a T-SQL variable which I want to convert to Julian Date Format and then insert in to a Numeric column in a table. Can someone please help?

I saw there are some posts on Julian Date but I am not able to get through.

EX :

declare @d datetime 
Set @d = GetDate()
select datediff(d, 0, @d) + 693596

The above statement does convert in to a julian date but not in the correct format. For example if today is 15 Feb 2014 then this should convert this in to 114046 but it converts this to 735279.

Also once this gets converted to correct julian format. I want to convert this to Numeric as I want to insert to a numeric column in a table.

I am sorry if this question has been asked previously. I am new to SQL Server 2005.

Any help will be appreciated

Regards

score 9 · Answer 1 · answered Feb 17 '14 at 00:12

9

Give this a go:

DECLARE @input_date DATETIME

SELECT @input_date = getdate()

SELECT datepart(year, @input_date) * 1000 + datepart(dy, @input_date)

answered Feb 17 '14 at 00:12

Guy Nethery

299
1
7

score 7 · Answer 2 · edited Sep 25 '17 at 20:58

The above answer doesn't seem to work for me.

--If Julian Format is defined as CYYJJJ where: 
--  C is the number of centuries since 1900-01-01 
--  YY is the 2 digit year 
--  JJJ is the day number of the given YY year

--Convert Date => Julian uning CYYJJJ

declare @date datetime = '02/15/2014' 
select (datepart(year, @date)-1900)*1000 + datepart(dy, @date) 
--output: 114046

--Convert Julian => Date using CYYJJJ

declare @jdate int = 114046 
select dateadd(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000 - 1900, 0))
--output: '02/15/2014'

---

--Convert Date => Julian uning YYYYJJJ

declare @dateB datetime = '02/15/2014' 
select (datepart(year, @dateB))*1000 + datepart(dy, @dateB) 
--output: 114046

--Convert Julian => Date using YYYYJJJ
declare @jdateB int = 2014046 
select dateadd(dd, (@jdateB - ((@jdateB/1000) * 1000)) - 1, dateadd(yy, @jdateB/1000 - 1900, 0)) 
--output: '02/15/2014'

I would suggest putting that into a Scalar Function so you can execute it like:

select dbo.FromJulianDate(2014046)

sourced from http://www.sqlservercentral.com/Forums/Topic778671-169-1.aspx

score 4 · Answer 3 · answered Jun 27 '18 at 13:52

The following will give an 7 character julian date output of: YYYYDDD

SELECT datepart(year, @input_date) * 1000 + datepart(dy, @input_date)

The following will give an 6 character julian date output output of: CYYDDD

SELECT CONCAT((DATEPART(year, @input_date) -1900),(DATEPART(dy, @input_date)))

Further explanation

Julian dates start from 1900-01-01
The C element is the century from 1900.
If the year is 1950, the C part will be 0
If the year is 2000, the C part will be 1 (and this increases with each 100 years)
The DDD element reflects the number of days from the beginning of the year so 28th February will be 058

score 2 · Answer 4 · edited Aug 24 '17 at 15:32

2

This should do what you are looking for:

SELECT CONCAT((DATEPART(year, @date) -1900),(DATEPART(dy, @date)))

edited Aug 24 '17 at 15:32

Jason Roman

8,146
10
35
40

answered Aug 24 '17 at 15:11

Alan Schmitz

21
1

score 2 · Answer 5 · answered Jul 25 '20 at 00:07

2

This should work:

DECLARE @date DATETIME;
SET @date = '2014-2-15';
SELECT @date,
       YEAR(@date),
       DATEPART(DAYOFYEAR, @date),
       (YEAR(@date) - 1900) * 1000 + DATEPART(DAYOFYEAR, @date) JulianDate;

answered Jul 25 '20 at 00:07

walterhuang

574
13
24

score 1 · Answer 6 · answered Jan 25 '22 at 21:39

I have somewhat of a simpler answer that I came up with.. You can edit what you want to display, with some simple replacing.

The query looks like this:

DECLARE @t_stamp datetime = GETDATE()
DECLARE @year char(4)   = RIGHT(DATEPART(year, @t_stamp),1) 
DECLARE @jday char(4)   = RIGHT('000'+CAST(DATEPART(DAYOFYEAR,  @t_stamp) AS varchar(3)),3)

SELECT RTRIM(@year) + @jday

Breakdown

Start off by declaring your starting datetime

DECLARE @t_stamp datetime = GETDATE()

Then set the year - starts from right to left

DECLARE @year char(4) = RIGHT(DATEPART(year, @t_stamp),1) 
--Changing '1' to '2' will give me 22 otherwise just 2.

then set your julian day (3 digits)

DECLARE @jday char(4) = RIGHT('000'+CAST(DATEPART(DAYOFYEAR, @t_stamp) AS varchar(3)),3)

Finally do your SELECT statement - you can append anything to this to make it unique or just cast it as an int.

SELECT  'SB-' + RTRIM(@year) + @jday

Your Result (Today's date 1/25/2022)

SB-2025

score 0 · Answer 7 · answered Apr 05 '21 at 23:50

(Since this is the highest ranked result in google) . . .
In case you need to turn this into an expression statement in a SSIS package, you can use this

(DT_WSTR, 50)(((YEAR(GETDATE())-1900)*1000)+DATEPART("dy", GETDATE()))

For example:

"SELECT somefield FROM " + @[User::FileHeaderTable] + " WHERE JdeDate = "+ (DT_WSTR, 50)(((YEAR(GETDATE())-1900)*1000)+DATEPART("dy", GETDATE()))

score 0 · Answer 8 · answered Apr 08 '21 at 15:48

Utilizing the known values here are two solutions I have personally utilized which I first did in NodeJS then converted to MSSQL when trying to get Julian Microsecond.

I had a known value in Gregorian and Julian Microsecond and so I just had to convert bridge the two and used https://www.aavso.org/jd-calculator as a check along the way. I used this answer to

/* Known Values:
   Gregorian: 2021-03-03 21:55:00.000
   Julian; 2459277.41319 ) 
   JulianMicrosecond: 212481593700000000
*/

I used this answer along the way to develop my Nodejs code.

let getJulainDay = (Year,Month,Day) => {
  a = parseInt((14 - Month) / 12);
  y = Year + 4800 - a;
  m = Month + 12 * a - 3;
  JDN =
    (Day +
    parseInt(
      ( 
      (153 * m + 2) / 5) +
      (365 * y) +
      parseInt(y / 4) -
      parseInt(y / 100) +
      parseInt(y / 400) -
      32045 )
    ) 
   //Into  Julian Microseconds 24*60*60*1e6 
    * 24 * 60 * 60 * 1000000; 
  return JDNMicroSeconds;
};

Then I mapped this into MSSQL that I had to treat a bit differently, noting that I had to enter all integer values in the formula above as floats or I would have had to cast each result.. it was just easier to enter them as floats.. if I don't do this I only get integer results and it throws the results off by years.

This is definitely an overly verbose breakdown but it allows you to see the steps along the way.

DECLARE @date DATETIME;

SET @date = '2021-03-03 21:55:00.000';
SELECT @date,
       YEAR(@date) as Year,
       DATEPART(MONTH,@date) as Month,
       DATEPART(DAY,@date) as Day,
       DATEPART(DAYOFYEAR, @date) as DofY,
       14 -(DATEPART(MONTH, @date)) as mm,
       ((14.0 -(DATEPART(MONTH, @date))) / 12.0) as A,
       (YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) as Y,
       ( DATEPART(MONTH,@date) + (12.0 * ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) -3.0) as M,
       /* Watch the order of operations/parethesis! */
       (DATEPART(DAY,@date) + 
       ((153.0 * (( DATEPART(MONTH,@date) + (12.0 * ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) -3.0)) + 2.0) / 5.0) + 
       (365.0 * ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)))) +
       ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) / 4.0) -
       ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) / 100.0) +
       ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) / 400.0) -
       32045.0) as JDN,
       (DATEPART(DAY,@date) + 
       ((153.0 * (( DATEPART(MONTH,@date) + (12.0 * ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) -3.0)) + 2.0) / 5.0) + 
       (365.0 * ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)))) +
       ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) / 4.0) -
       ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) / 100.0) +
       ((YEAR(@date) + 4800.0 - ((14.0 -(DATEPART(MONTH, @date))) / 12.0)) / 400.0) -
       32045.0) * 24 * 60 * 60 * 1000000 as JDNMicroSecond,
       /* What others proposed which I do not believe is correct. */
       (YEAR(@date) - 1900) * 1000 + DATEPART(DAYOFYEAR, @date) IncorrectJDN;

The output being:

Convert a Date to Julian Date and then Store in a Numeric Field in SQL Server 2005

8 Answers8

Linked