将儒略日CYYDDD转换为DD/MM/YYYY

Question

我在SQL Server的一张表中有一个字段，使用朱利安日期格式（CYYDDD）存储日期。我正在该字段上运行select SQL，并希望将日期格式转换为DD/MM/YYYY。

我尝试了几个SQL语句进行格式转换，但没有一个能给出正确的输出结果。

DATEADD(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000, 0))

DATEADD(year, XHUPMJ / 1000, 0) + DATEADD(day, XHUPMJ % 1000, 0) - 1

希望以DD/MM/YYYY的格式输出日期，不要有任何后续的0。

我在列中有一个儒略日值，如115351，我期望它转换为17/12/2015。

- Sam

---

请问您能否提供一个儒略日的样例值以及您所需格式下的期望值？ - Arulkumar

Answer 1

我从这个网站找到了一段示例代码，用于将儒略日转换为DD/MM/YYYY格式。

DECLARE @Sample AS VARCHAR(6) = '115351'
SELECT CONVERT(VARCHAR(10), DATEADD(DAY, CONVERT(INT, @Sample) - ((1000*(CONVERT(INT, @Sample)/1000)))-1, DATEADD(YEAR, CONVERT(INT, @Sample/1000), '1 Jan 1900')), 103) AS Result

它将转换2015年的第351天，因此结果将是17/12/2015

在db<>fiddle上的演示

---

更新：

正如cars10m在评论中建议的那样，使用%取模运算符，上面的查询可以简化为

DECLARE @Sample AS VARCHAR(6) = '115351'
SELECT CONVERT(VARCHAR(10), DATEADD(DAY, CONVERT(INT, @Sample) % 1000 -1, DATEADD(YEAR, CONVERT(INT, @Sample/1000), '1 Jan 1900')), 103) AS Result

- Arulkumar

---

不幸的是，它给了我这样的输出.. 2015-12-17 00:00:00.000。有没有办法让我只看到日期，格式为17/12/2015。 - Sam

一般来说，查询应该返回日期或日期时间类型，这也正是它的数据类型。该日期时间类型的格式化应在你所使用的工具中完成。如果将其返回为格式化字符串，那么其他工具很难选择日期、对其排序等操作。 - TomC

@Sam，请检查我的更新查询或演示，它将以“DD/MM/YYYY”格式返回输出。 - Arulkumar

@ArulKumar，太好了！！！我得到了预期的输出。非常感谢您的帮助。 - Sam

从 SQL-Server R2008 开始，您可以使用模数计算，并将 CONVERT(INT, @Sample) - ((1000*(CONVERT(INT, @Sample)/1000))) 简化为 CONVERT(INT, @Sample) %1000。 - Carsten Massmann

@cars10m 谢谢您的建议，已经在答案中更新了您的观点。 - Arulkumar

Answer 2

只是把我的老帽子扔进了一个旧环中。这是@Arulkumar发布的变体，但不需要任何INT转换，因为它从INT开始。

--===== This would be the parameter for an iTVF.
DECLARE @JdeDate INT = 115351    
;
--===== Formula to convert the JDE date to a normal DATETIME and then format it to 
     -- If you want it to be a DATE datatype, convert it one more time.
     -- "-1" is the DATE SERIAL NUMBER for the day before 1900-01-01, which effective subtracts 1 day.
 SELECT [DD/MM/YYYY] = CONVERT(CHAR(10),DATEADD(dy,@JdeDate%1000,DATEADD(yy,@JdeDate/1000,-1)),103)
;

如果您的语言设置正确地设置了DATEFORMAT，则最终的CONVERT可能是不必要的。

上面代码的结果看起来像这样...

当然，您可以选择任何列名称，并且我建议将其转换为iTVF（内联表值函数）。如果您现在拥有2019或更好的版本，它可能作为内联标量函数工作，但我尚未测试过。

- Jeff Moden

Answer 3

这是您想要的答案吗？

SELECT DATEADD(day, CAST(RIGHT(Variable,3) AS int) / 1, CONVERT(datetime,LEFT(Variable,2) + '0101', 103))

- prince nhiel cada

---

不，我在该列中的值为115351，我想将其转换为17/12/2015。 - Sam

我得到的输出是2011-12-17 00:00:00.000。但这不是正确的日历日期，我正在寻找以17/12/2015格式输出。非常感谢您的时间和努力。 - Sam