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 Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Vista, etc.




download SQL Server 2014



How to Convert Gregorian To Julian 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 Gregorian Julian converter function.



T-SQL User Defined Function to Convert Gregorian To Julian Date Format


Converting Gregorian to Julian 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 gregorian to julian converter for later use in your t-sql codes.

The Gregorian to Julian 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.ConvertGregorianToJulianDate
(
  @Gregorian datetime
) RETURNS INTEGER
AS
BEGIN

DECLARE @DDD varchar(3)

SELECT @DDD =
  CAST(
    DATEDIFF(
      dd,
      CAST(YEAR(@Gregorian) AS CHAR(4)) + '0101',
      @Gregorian
    ) + 1
    AS CHAR(3)
  )

RETURN
  CAST(YEAR(@Gregorian) AS CHAR(4)) +
  REPLICATE('0', 3-LEN(@DDD)) +
  @DDD

END




Sample SQL Code to Convert Gregorian Date to Julian Date on MS SQL Server


Here is a sql sample code displaying how you can use the t-sql user defined function dbo.ConvertGregorianToJulianDate in a sample for converting a datetime value in Gregorian date format to Julian date format.

DECLARE @Gregorian datetime
SET @Gregorian = GETDATE()
SELECT dbo.ConvertGregorianToJulianDate(@Gregorian)
SELECT
  dbo.ConvertJulianToGregorian(
    dbo.ConvertGregorianToJulianDate(@Gregorian)
  )

You can find a t-sql user defined function for converting Julian to Gregorian date at "How to Convert Julian To Gregorian Date Format using T-SQL User Defined Function with Sample SQL Code".

I hope you will find the sql function ConvertGregorianToJulianDate useful for converting Gregorian to Julian calendar dates.






Follow Kodyaz on Twitter

Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums



Free Exam Vouchers









Copyright © 2004 - 2014 Eralper Yilmaz. All rights reserved.
Community Server by Telligent Systems