尝试将7位数的儒略日期转换为MMDDYY格式

Question

我正试图在SQL Server中将7位julian/大型机日期转换为mmddyy格式的日历日期。例如julian日期2005020，其中前4位是年份2005，后三位是日历中的日期，因此020=1月20日。所以我需要在SQL Server中将此日期设置为012005(MMDDYY(。

我一直在使用以下查询，但在它加载了一些记录后不断出现错误：

SELECT DATEADD(day,CAST(RIGHT([julianDateColumn],3) as int)-,LEFT([julianDateColumn],4))

我得到的错误：

从字符串转换日期和/或时间时转换失败。

最初我是在Access DB中使用"；DATESERIAL"；函数，但从我所看到的与SQL Server中最接近的是"；DATEFROMPRTS"；，我尝试使用以下公式，但它也不起作用：

DATEFROMPARTS([julianDateColumn]/1000,1,[julianDateColumn] % 1000)

提前感谢！

Answer 1

最简单的方法似乎是将左边作为年份，然后加上天数(-1(来确定日期。此外，我将直接使用date数据类型，而不是使用MMDDYY格式。如果你想要一个特定的格式，那是为你的表现层。

SELECT JulianDate,
CONVERT(date,DATEADD(DAY,RIGHT(JulianDate,3)-1,CONVERT(datetime,LEFT(JulianDate,4)))) AS ActualDate --4 int strings are iterpreted as the year, so I'm going to take advantage of that
FROM (VALUES('2005020'))V(JulianDate);

---

根据对答案的评论，OP似乎有一些日期不符合所述格式(yyyyddd(。因此，我们可以在这里使用一个日历表，在这里，然后LEFT JOIN到它，看看你得到了哪些坏行(INNER JOIN得到了日期(。

你可以用这样的东西创建表格：

CREATE TABLE dbo.CalendarTable (CalendarDate date NOT NULL PRIMARY KEY,
CalenderYear AS DATEPART(YEAR, CalendarDate) PERSISTED,
CalenderMonth AS DATEPART(MONTH, CalendarDate) PERSISTED,
CalenderDay AS DATEPART(DAY, CalendarDate) PERSISTED,
CalenderMonthName AS DATENAME(MONTH, CalendarDate),
JulianDate AS DATEPART(YEAR,CalendarDate) * 1000 + DATEDIFF(DAY,DATEFROMPARTS(DATEPART(YEAR, CalendarDate),1,1),CalendarDate) + 1 PERSISTED); --Some example columns

WITH N AS(
SELECT N
FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
Tally AS(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 AS I
FROM N N1, N N2, N N3, N N4, N N5, N N6),
Dates AS(
SELECT CONVERT(date,DATEADD(DAY, T.I, '19000101')) AS CalendarDate
FROM Tally T)
INSERT INTO dbo.CalendarTable(CalendarDate)
SELECT CalendarDate
FROM Dates
WHERE CalendarDate < '21000101';
GO

然后我们可以做这样的事情来获得坏行：

SELECT YT.JulianDate
FROM dbo.YourTable YT
LEFT JOIN dbo.CalendarTable CT ON YT.JulianDate = CT.JulianDate
WHERE CT.JulianDate IS NULL;

Answer 2

我想我会使用datefromparts()和dateadd():

select dateadd(day,
right(juliandate, 3) - 1),
datefromparts(left(juliandate, 4), 1, 1)
)

Answer 3

select Format(cast(concat(substring('2005020', 1, 4), '-', Month(cast(substring('2005020', 5, len('2005020')) as int)) ,'-', day(dateadd(day,-1,cast(substring('2005020', 5, len('2005020')) as int ) )  )) as date), 'MMddyy')