Convert Julian Date to YYYY-MM-DD

Question

I have searched far and wide, but I can't seem find a way to convert julian to yyyy-mm-dd.

Here is the format of my julian:

The Julian format consists of the year, the first two digits, and the day within the year, the last three digits.

For example, 95076 is March 17, 1995. The 95 indicates the year and the 076 indicates it is the 76th day of the year.

I have tried this but it isn't working:

dateadd(d,(convert(int,LAST_CHANGED_DATE) % 1000)-1, convert(date,(convert(varchar,convert(int,LAST_CHANGED_DATE) /1000 + 1900) + '/1/1'))) as GrgDate

Possible duplicate of [Convert 7 digit Julian Date to DateTime in SQL?](http://stackoverflow.com/questions/16279457/convert-7-digit-julian-date-to-datetime-in-sql) — Giorgi Nakeuri, Nov 05 '15 at 16:52
no, that jd is different than the one i am trying to convert. I tried this and it didn't work — Mochael_DLite, Nov 05 '15 at 16:56

MikeVe · Accepted Answer · 2015-11-05T17:07:23.487

9

You can select each part of the date using datepart()

SELECT DATEPART(yy, 95076), DATEPART(dy, 95076)

+++EDIT: I misunderstood something. Here's my correction: +++++

SELECT DATEADD(day, CAST(RIGHT('95076',3) AS int) – 1, CONVERT(datetime,LEFT('95076',2) + '0101', 112))

edited Nov 05 '15 at 17:07

answered Nov 05 '15 at 16:54

MikeVe

1,062
8
13

Dmytro Shevchenko · Answer 2 · 2015-11-05T17:00:15.627

1

Edit: leaving this answer for Oracle and MySQL users
This will not work in T-SQL.

Use this:

MAKEDATE(1900 + d / 1000, d % 1000)

For example:

SELECT MAKEDATE(1900 + 95076 / 1000, 95076 % 1000)

This returns March, 17 1995 00:00:00.

SQLFiddle

edited Nov 05 '15 at 17:00

answered Nov 05 '15 at 16:57

Dmytro Shevchenko

33,431
6
51
67

1

MAKEDATE() is Oracle and MySql only. It is not part of the standard. – Joel Coehoorn Nov 05 '15 at 16:58
@JoelCoehoorn You are correct. I'll leave this here in case a MySQL or Oracle user comes along. – Dmytro Shevchenko Nov 05 '15 at 17:01

score 0 · Answer 3 · answered Nov 05 '15 at 17:00

0

I concatenated 20 to my JD and then ran DATEADD(YEAR, LAST_CHANGE_DATE / 1000 - 1900, LAST_CHANGE_DATE % 1000 - 1)

this got me the result. Thank you!!!

answered Nov 05 '15 at 17:00

Mochael_DLite

167
2
5
14

score 0 · Answer 4 · answered Feb 22 '19 at 12:51

0

FOR SQL Users DECLARE @jdate VARCHAR(10) SET @jdate = 117338 SELECT dateadd(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000, 0))

answered Feb 22 '19 at 12:51

Mitul Panchal

592
5
7

score 0 · Answer 5 · answered Mar 27 '19 at 14:23

0

This will definitely work in all case.

DECLARE @date int
SET @date = 21319
SELECT DATEADD(dd, RIGHT(@date,LEN(@date)-3)-1, DATEADD(yy,LEFT(@date,1)*100 +RIGHT(LEFT(@date,3),2),'1 Jan 1900'))

answered Mar 27 '19 at 14:23

Mitul Panchal

592
5
7

score 0 · Answer 6 · edited Sep 25 '20 at 14:29

0

You need to specify 1 or 0 for century in first character. For example, SET @date = 21319 should be prefixed with 1 or 0. Below is an example that will work with all y2k use cases.

DECLARE @jdate INT 
SET @jdate = 119150 
SELECT DATEADD(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, DATEADD(yy, @jdate/1000, 0))

edited Sep 25 '20 at 14:29

Obsidian

3,719
8
17
30

answered Sep 25 '20 at 13:21

John Portnov

1

score 0 · Answer 7 · answered Oct 03 '20 at 15:49

0

Declare @Julian varchar(7)

Declare @date date

Set @Julian = 2020277

Set @Date = Dateadd(day,+ Cast(right(@Julian,3) as int)-1, Cast(left(@Julian,4) + '0101' as Date))

Select @Date

answered Oct 03 '20 at 15:49

Raynald

1

1

This question was answered four years ago. While your answer isn't identical to the accepted answer, it is quite close. Can you edit your answer to provide an explanation for why this approach is preferred over the accepted answer? Or, at least, under what conditions a reader might want to use your approach? Thank you. – Jeremy Caney Oct 03 '20 at 18:16

score 0 · Answer 8 · edited Dec 05 '20 at 15:34

0

Standard SQL is simple (you can do similar for 2 digit year YYMMDD)

Declare @julDate int = 2020275

Select 
DateAdd
(
   day
   , Right(@julDate,3)-1
   , Cast((Left(@julDate,4)+'-01-01') as smalldatetime)
)

edited Dec 05 '20 at 15:34

klediooo

630
1
7
25

answered Dec 04 '20 at 10:44

Ash Pardy

1

score 0 · Answer 9 · edited Aug 31 '21 at 12:13

0

Try this out:

DECLARE @jdate int

SET @jdate = 21243

select dateadd(dd, (@jdate - ((@jdate/1000) * 1000)) - 1, dateadd(yy, @jdate/1000+100, 0))

edited Aug 31 '21 at 12:13

Adrian Mole

49,934
160
51
83

answered Aug 31 '21 at 11:45

Inaam Muaz

1
2

Convert Julian Date to YYYY-MM-DD

9 Answers9

Linked