MS SQL Function to Convert the Julian Date to the SQL Date Format

Below I put the query for a SQL Function which converts the Julian
date in int data type to SQL Date format.

IF EXISTS (SELECT * FROM sys.objects WHERE object_id =
OBJECT_ID(N'dbo.Convert_Julian') AND TYPE IN (N'FN', N'IF', N'TF',
N'FS', N'FT'))

DROP FUNCTION dbo.Convert_Julian

GO

CREATE FUNCTION dbo.Convert_Julian(@idate INT)
Returns DATE
WITH EXECUTE AS CALLER

AS

BEGIN
DECLARE @oDate DATE, @year VARCHAR(10),@intYear INT,@sdate
VARCHAR(10),@noOfDays VARCHAR(10),@intNoOfDays INT,@month INT,@day
INT,@sMonth VARCHAR(10),@sDay VARCHAR(10),@sMonthc VARCHAR(10),@sDayc
VARCHAR(10)

SELECT @sdate=CONVERT(VARCHAR(50),@idate)

IF (@idate=0)

SET @oDate = CAST(0 AS DATETIME)

ELSE BEGIN
SET @year=SUBSTRING(@sdate,1,4)
SET @noOfDays=SUBSTRING(@sdate,5,3)
SELECT @intYear=CONVERT(INT,@year)
SELECT @intNoOfDays=CONVERT(INT,@noOfDays)

IF (@intNoOfDays<=31)
BEGIN
SELECT @month=1

IF(@intNoOfDays=31)
SELECT @day=31

ELSE
SELECT @day=@intNoOfDays
END

IF(@intNoOfDays>31 AND @intNoOfDays <= 59)
BEGIN
SELECT @month=2

IF(@intNoOfDays=59)
SELECT @day=28
ELSE
SELECT @day=@intNoOfDays-31

END

IF(@intNoOfDays>59 AND @intNoOfDays<=90)
BEGIN
SELECT @month=3

IF(@intNoOfDays=90)
SELECT @day=31
ELSE
SELECT @day=@intNoOfDays-59
END

IF(@intNoOfDays>90 AND @intNoOfDays<=120)
BEGIN
SELECT @month=4

IF(@intNoOfDays=120)
SELECT @day=30
ELSE
SELECT @day=@intNoOfDays-90
END

IF(@intNoOfDays>120 AND @intNoOfDays<=151)
BEGIN
SELECT @month=5

IF(@intNoOfDays=151)
SELECT @day=31
ELSE
SELECT @day=@intNoOfDays-120
END

IF(@intNoOfDays>151 AND @intNoOfDays<=181)
BEGIN
SELECT @month=6

IF(@intNoOfDays=181)
SELECT @day=30
ELSE
SELECT @day=@intNoOfDays-151
END

IF(@intNoOfDays>181 AND @intNoOfDays<=212)
BEGIN
SELECT @month=7

IF(@intNoOfDays=212)
SELECT @day=31
ELSE
SELECT @day=@intNoOfDays-181
END

IF(@intNoOfDays>212 AND @intNoOfDays<=243)
BEGIN
SELECT @month=8

IF(@intNoOfDays=243)
SELECT @day=31
ELSE
SELECT @day=@intNoOfDays-212
END

IF(@intNoOfDays>243 AND @intNoOfDays<=273)
BEGIN
SELECT @month=9

IF(@intNoOfDays=273)
SELECT @day=30
ELSE
SELECT @day=@intNoOfDays-243
END

IF(@intNoOfDays>273 AND @intNoOfDays<=304)
BEGIN
SELECT @month=10

IF(@intNoOfDays=304)
SELECT @day=31
ELSE
SELECT @day=@intNoOfDays-273
END

IF(@intNoOfDays>304 AND @intNoOfDays<=334)
BEGIN
SELECT @month=11

IF(@intNoOfDays=334)
SELECT @day=30
ELSE
SELECT @day=@intNoOfDays-304
END

IF(@intNoOfDays>334 AND @intNoOfDays<=365)
BEGIN
SELECT @month=12

IF(@intNoOfDays=365)
SELECT @day=31
ELSE
SELECT @day=@intNoOfDays-334
END


SELECT @sMonth=CONVERT(VARCHAR(10),@month)
SELECT @sDay=CONVERT(VARCHAR(10),@day)

IF(LEN(@day)=1)
SELECT @sDayc='0'+@sDay
ELSE
SELECT @sDayc=@sDay


IF(LEN(@month)=1)
SELECT @sMonthc='0'+@sMonth
ELSE
SELECT @sMonthc=@sMonth

SET @oDate=CONVERT(DATE,@year+'.'+@sMonthc+'.'+@sDayc,102)

END

RETURN @oDate

END

GO

Then you can get the converted date value by executing a simple query as given below

SELECT dbo.Convert_Julian(JulianDateField) FROM YOURTABLE

If you have any issues please put a comment