How to Convert Julian To Gregorian Date Format using T-SQL User Defined Function with Sample SQL Code
Julian date YYDDD format : In Julian date, date values are displayed in YYYYDDD or YYDDD formats.
The sample t-sql function is a julian Gregorian converter function.
T-SQL User Defined Function to Convert Julian To Gregorian Date Format
Converting Julian to Gregorian calendar dates can be implemented by using a user defined t-sql function.
The advantage of using a sql function is you can use this julian to gregorian converter for later use in your t-sql codes.
The Julian to Gregorian conversion should take into consideration the leap years. While developing on SQL Server by using the build-in t-sql datetime functions, developers do not need to code any specific code for leap years.
The sql engine will take care of the leap years, 29 days in February every 4 year.
CREATE FUNCTION dbo.ConvertJulianToGregorianDate
(
@JulianDate VARCHAR(7)
) RETURNS DATETIME
AS
BEGIN
IF LEN(@JulianDate) = 5
SET @JulianDate = '20' + @JulianDate
DECLARE @GregorianDate DATETIME
DECLARE @ZeroDate datetime
SET @ZeroDate = DATEADD(yy, -1 * DATEDIFF(yy, 0, GETDATE()), GETDATE())
DECLARE @Year Int
SET @Year = CAST(LEFT(@JulianDate, 4) AS Integer)
DECLARE @Days Int
SET @Days = CAST(RIGHT(@JulianDate, 3) AS Integer)
SELECT @GregorianDate =
DATEADD(
dd,
@Days - 1,
DATEADD(
yy,
@Year - YEAR(@ZeroDate),
0
)
)
RETURN @GregorianDate
END
Sample SQL Code to Convert Julian Date to Gregorian Date on MS SQL Server
Here is a sql sample code displaying how you can use the t-sql user defined function dbo.ConvertJulianToGregorianDate in a sample for converting a datetime value in Julian date format to Gregorian date format.
DECLARE @JulianDate CHAR(7)
SET @JulianDate = '2008366'
SELECT
dbo.ConvertJulianToGregorianDate(@JulianDate),
CONVERT(VARCHAR(20), dbo.ConvertJulianToGregorianDate(@JulianDate), 101)
You can find a t-sql user defined function for converting Gregorian to Julian date at "How to Convert Gregorian To Julian Date Format using T-SQL User Defined Function with Sample SQL Code".
I hope you will find the sql function ConvertJulianToGregorianDate useful for converting Julian to Gregorian calendar dates.
|