Convert a Date to Julian Date and then Store in a

问题:

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

回答1:

Give this a go:

DECLARE @input_date DATETIME

SELECT @input_date = getdate()

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

回答2:

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

回答3:

This should do what you are looking for:

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

回答4:

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