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

Answer 1

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 

Answer 2

Give this a go:

DECLARE @input_date DATETIME

SELECT @input_date = getdate()

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

Answer 3

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

Answer 4

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;