SQL Server T-SQL User-Defined Function of the Week

Converting From a Julian Date

Check out the UDF Frequently Asked Questions at: http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF, send e-mail to udf@NovickSoftware.com
 

Last week's function udf_DT_ToJulian introduced the Julian format as a way to represent a date. A Julian date is usually defined as the number of days since an origin day. When working with SQL Server it's a good idea to use the same origin date for a Julian as SQL Server uses for datetime and smalldatetime: January 1, 1900.

Last week's function converted from a datetime to a Julian date. This week's function is the converse. It converts from a Julian date to a datetime. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE  FUNCTION udf_DT_FromJulian (

    @JulianDays int -- Days from 1900-1-1
)   RETURNS datetime
    WITH SCHEMABINDING
/* 
* Returns a datetime from a Julian date.  The julian date is the
* number of days since 1990-01-01.  Will not work before 1753.
*
* Equivalent Template:DATEADD (dd, @JulianDays, '1990-01-01')
*
* Example:
select dbo.udf_DT_FromJulian(37664)
* Test:
PRINT 'Test 1    ' +CASE WHEN '2003-02-14' = 
  dbo.udf_DT_FromJulian(37664) THEN 'Worked' ELSE 'ERROR' END
*      
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of Week Newsletter Vol 2 #3 12/2/03 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

    RETURN DATEADD (dd, @JulianDays, '1900-01-01')
END

GO


GRANT EXEC, REFERENCES on dbo.udf_DT_FromJulian to [Public]
GO

Let's take a look at a few results from the function:

SELECT dbo.udf_DT_FromJulian (37955)  as [Today]
     , dbo.udf_DT_FromJulian (0)      as [Origin]
GO

SELECT dbo.udf_DT_FromJulian (-32142) as [Old Date] 
     , dbo.udf_DT_FromJulian (36524)  as [Y2K]
GO

Today                    Origin 
------------------------ ----------------------- 
2003-12-02 00:00:00.000  1900-01-01 00:00:00.000 

Old Date                 Y2K                    
------------------------ -----------------------
1812-01-01 00:00:00.000  2000-01-01 00:00:00.000

For good measure, let's check to be sure that udf_dt_ToJulian and udf_DT_FromJulian are truly opposite functions:

SELECT dbo.udf_DT_FromJulian (dbo.udf_DT_ToJulian ('2003-12-02'))
               as [Today]
     , dbo.udf_DT_FromJulian (dbo.udf_DT_ToJulian ('1900-01-01'))
               as [Origin]
GO

SELECT dbo.udf_DT_FromJulian (dbo.udf_DT_ToJulian ('1812-01-01'))
               as [Old Date] 
     , dbo.udf_DT_FromJulian (dbo.udf_DT_ToJulian ('2000-01-01'))
               as [Y2K]
GO

Today                    Origin 
------------------------ ----------------------- 
2003-12-02 00:00:00.000  1900-01-01 00:00:00.000 

(Results - second set)
Old Date                 Y2K                    
------------------------ -----------------------
1812-01-01 00:00:00.000  2000-01-01 00:00:00.000

So it looks like the functions work.

I tend to use Julian dates when there is a need to store a date without the time combined with the need to frequently add or take the differences between dates.

Due to the improvements in SQL Server's built-in and user-defined functions, using Julian dates isn't absolutely necessary. You can archive the same functionality with datetime or smalldatetime. But Julian dates can make some jobs easier and they're already built into many systems.

My book T-SQL UDFs is available on my web site bundled with the Bonus-100 library of UDFs. I've adjusted the shipping prices and can now ship to the entire world.

Warning: This function and all functions that use SQL Server's datetime functions such as DATEADD won't work on dates earlier than 1753.