Monday, May 7, 2012

Julian to iso date conversion function

Recently I had helped out a friend on logic for converting Julian date value to iso date value in T-SQL. Julian dates are in format CYYDDD where C will denote century YY year and DDD the day of year. So a date value of 6th May 2012 will be represented as 112127. The following is the logic you can apply to get iso date value from Julian date representation.


CREATE FUNCTION JulianDateConv
(
 @JulianDate int
)
RETURNS date AS
BEGIN 
   DECLARE @ISODate date
   SELECT @ISODate = DATEADD(dd,(@JulianDate  % 1000)-1,DATEADD(yy,@JulianDate / 1000,0))
   RETURN @ISODate
END


and execute it like


SELECT dbo.JulianDateConv(112127)