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