将日期转换为儒略日期，然后存储在 SQL Server 2005 中的数字字段中 [英] Convert a Date to Julian Date and then Store in a Numeric Field in SQL Server 2005

问题描述

我在 T-SQL 变量中有一个日期字段，我想将其转换为 Julian 日期格式，然后插入到表中的 Numeric 列中.有人可以帮忙吗?

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?

我看到 Julian Date 上有一些帖子，但我无法通过.

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

前:

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

上述语句确实转换为儒略日期，但格式不正确.例如，如果今天是 2014 年 2 月 15 日，那么这应该将其转换为 114046，但它会将其转换为 735279.

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.

此外，一旦将其转换为正确的朱利安格式.我想将其转换为数字，因为我想插入到表中的 numeric 列.

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.

如果之前有人问过这个问题，我很抱歉.我是 SQL Server 2005 的新手.

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

任何帮助将不胜感激

问候

推荐答案

以上答案似乎对我不起作用.

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)

来自 http://www.sqlservercentral.com/Forums/Topic778671-169-1.aspx

这篇关于将日期转换为儒略日期，然后存储在 SQL Server 2005 中的数字字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！