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

Converting to 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

Sign up for this newsletter at: http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

Transact-SQL User-Defined Functions has been published!  Take a look at it right now!

There are many formats used to store date and time information and this issue is about one of them, the Julian date. It's usually defined as the number of days since an origin day but I've seen people use the day of the year and call that the Julian date.

The US Navy, an organization that has an driving concern to answer the question, "What time is it?" uses January 1, 4713 BCE as their origin date. You can view their calculator and read about how they calculate the date at this link:
http://aa.usno.navy.mil/data/docs/JulianDate.html

When you work with dates that are earlier than 1753 you'll have to handle changes in the calendar that have occurred since then. Unfortunately, the last set of changes didn't all happen at the same time as Pope Gregory had hoped so the date isn't really the same all over the European world.

In any case 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.

Unfortunately, other Microsoft code, such as VBScript, Visual Basic and .Net use December 30, 1899 as the origin. Go figure?

The purpose of this week's UDF is to convert a datetime value to the Julian date, which is an int. Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE   FUNCTION dbo.udf_DT_ToJulian (

    @Date datetime -- Date to convert to Julian  
)   RETURNS int -- Julian date.  # days since 1900-01-01
    WITH SCHEMABINDING -- This is a deterministic function.
/* 
* Returns the Julian date.  The number of days since 1990-01-01.
*
* Equivalent Template:DATEDIFF(dd,'1990-01-01',)
*
* Example:
select dbo.udf_DT_ToJulian('2003-02-14')
* Test:
PRINT 'Test 1    ' +CASE WHEN x=dbo.udf_DT_ToJulian('2003-02-14')
                     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 #2 11/26/03 
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN
           -- Explicit CONVERT with the format code is needed
           -- in order to make this UDF deterministic.
    Return DATEDIFF (day
                   , CONVERT(datetime, '1900-01-01', 110)
                   , @Date)
END


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

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

SELECT dbo.udf_DT_ToJulian ('2003-11-26') as [Today]
     , dbo.udf_DT_ToJulian ('1900-01-01') as [Origin]
     , dbo.udf_DT_ToJulian ('1812-01-01') as [Old Date] 
     , dbo.udf_DT_ToJulian ('2000-01-01') as [Y2K]
GO

Today       getdate     Origin      Old Date    Y2K         
----------- ----------- ----------- ----------- ----------- 
      37949       37949           0      -32142       36524 

One of the reasons for using the Julian date is that it drops all time values and only stores the date. You can see that in the column [getdate]. The lack of time makes Julian dates simple to work with in systems that don't care about the time. And of course, calculating the difference between dates is trivial, just subtract them.

Can you guess what function is coming up next week? Of course, it's the converse function udf_DT_FromJulian.

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.

For those of you in the US, have a great Thanksgiving.