sql - Trying to convert a 7 digit julian date into MMDDYY format

Question

I'm trying to convert a 7 digit julian/mainframe date into a calendar date of format mmddyy in SQL Server. An example of this being julian date 2005020, where the first 4 digits are the year, 2005, and the last three are the day number in a calendar, so 020 = january 20. So I'd need to get this date as 012005(MMDDYY) in SQL Server.

I've been using the following query but keep getting an error after it loads a few records:

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

The error I've been getting:

Conversion failed when converting date and/or time from character string.

Originally I was doing this in an Access DB using the "DATESERIAL" function but from what I've seen the closest thing to that in SQL Server was "DATEFROMPARTS", I tried using the following formula but it also didn't work:

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

Thanks in advance!

标签： sqlsql-serverdatetimejulian-date

Answer 1

The simplest would seem to be to take the left as the year, and the add the days (-1) to make a date. Also, rather than using a format of MMDDYY I'm going to go straight a date datatype. If you want it in a specific format, that's for your presentation layer.

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);

---

Based on the comments on the answer, it appears that the OP has some dates that don't conform to the format that stated (yyyyddd). Therefore what we could use here is a calendar table, here, and then LEFT JOIN to it and see what bad rows you get (and INNER JOIN to get the dates).

You can create the table with something like this:

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

Then we can do something like this to get the bad rows:

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