SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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
Code




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

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.