Convert Julian Date CYYDDD to DD/MM/YYYY

Question

I have a column in a table in SQL Server which stores date in Julian format (CYYDDD). I'm running a select SQL on the field and would like to convert the date format to DD/MM/YYYY.

Have tried few SQLs for the format conversion, but none of them give me a correct output

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

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

Would like to see date output in the format DD/MM/YYYY without any following 0's

I have a Julian Date value in the column as 115351 and I'm expecting it to convert to 17/12/2015

Can you please share a sample value for Julian date and the expected value in your required format? — Arulkumar, Jun 13 '19 at 02:52

Arulkumar · Accepted Answer · 2019-06-13T05:15:08.100

4

I found a sample code from this site, to convert the Julian Date to DD/MM/YYYY format

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

it will convert the 351 th day of the year 2015, so the result will be 17/12/2015

Demo on db<>fiddle

UPDATE:

As cars10m suggested in the comment, using % Modulus operator, the query above can be reduced as

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

edited Jun 13 '19 at 05:15

answered Jun 13 '19 at 03:10

Arulkumar

12,966
14
47
68

Unfortunately, it gave me output like this .. 2015-12-17 00:00:00.000. Is there a way I can see only the date in the format 17/12/2015. – Sam Jun 13 '19 at 03:50
In general, the query should return a date or datetime, which this one is. The formatting of that should be done in whatever tool you are using. if you bring it back as a formatted string it makes it hard for other tools to select dates, sort them, etc. – TomC Jun 13 '19 at 04:30
@Sam Please check my updated query or demo, it will return the output in `DD/MM/YYYY` format – Arulkumar Jun 13 '19 at 04:49
@ArulKumar, that worked !!! I got the output as expected. Thank you so much for your inputs. – Sam Jun 13 '19 at 04:57
2

As of SQL-Server R2008 you can use modulo calculation and shorten `CONVERT(INT, @Sample) - ((1000*(CONVERT(INT, @Sample)/1000)))` to `CONVERT(INT, @Sample) %1000`. – Carsten Massmann Jun 13 '19 at 05:11
@cars10m Thanks for the suggestion, updated your point in the answer. – Arulkumar Jun 13 '19 at 05:15

prince nhiel cada · Answer 2 · 2019-06-13T03:57:07.940

0

Is this answer you want?

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

edited Jun 13 '19 at 03:57

answered Jun 13 '19 at 02:55

prince nhiel cada

74
12

No.. the value I have in the column is 115351 and I'm looking to convert it to 17/12/2015. – Sam Jun 13 '19 at 03:07
I got the output like this 2011-12-17 00:00:00.000. But this is not the correct calendar date and I'm looking for output in the format 17/12/2015. Really appreciate your time and efforts. – Sam Jun 13 '19 at 03:59

score 0 · Answer 3 · answered Dec 19 '22 at 07:26

Just throwing my old hat into an old ring. It's a variation of what @Arulkumar posted but doesn't require any INT conversions because it starts as an 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)
;

The final CONVERT may not be necessary if the DATEFORMAT is setup correctly by your Language setup.

The results from the code above look like this...

Of course, you can pick any column name you'd like and I do recommend turning it into an iTVF (Inline Table Valued Function). If you have 2019 or better now, it might work as an inlined scalar function but I've not tested it.

Convert Julian Date CYYDDD to DD/MM/YYYY

3 Answers3