Find the Number of Hours in a Day. It isn't always 24!

Before we get started, I want to alert any readers who live in the Boston area that I'll be speaking at the New England Visual Basic Professional User Group on April 1st in Microsoft's Waltham office on Jones Road. The talk is titled, "Building on the .Net Framework: Creating Professional WinForms Applications". It's about how to bridge the gap between where we start with the .Net framework and the goal of a supportable, robust business application constructed with the .Net framework. The meeting will be from 6:30 to 8:30 and everyone is welcome.

In the Northern Hemisphere the Sun out later and later these days and it' about time for the change over to daylight savings time. I was reminded of this as I was reading an article on MSDN about handling dates in C#. One of the author's observations was that every day doesn't have the same number of hours! What? Don't they all have 24 hours? For measurement purposes, the day that daylight savings time begins has only 23 hours and the day when daylight savings ends has 25 hours. This would be relevant when converting a measurement that was taken in days to a smaller unit such as minutes. So if we had a measurement of 1,500,000 page views on our web site and we wanted to convert it to page views per second, we'd need to know how many hours in the day to get the correct conversion.

This may or may not be relevant to your application, it depends on the context. In any case, I decided to write the function udf_DT_HoursInDayNA, which is this week's UDF.

The schedule for daylight savings time varies around the world. That's why I've added the NA characters to the end of the function name. You may have to change the function to match your local conditions. However, the function does check the registry to see if daylight savings is enabled on this computer and returns 24 if it's not.

You may recall from last week's issue that I mentioned that I need to publish udf_DT_NthDayInMon before I published this week's UDF. That was because I thought that I'd need it to find the first Sunday in April and the last Sunday in October. However, as you'll see, it was possible to use other facts about the calendar to eliminate the need for calling any other UDFs.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION  dbo.udf_DT_HoursInDayNA (
    @OnDate datetime -- the date we're interested in
)    RETURNS smallint -- Number of hours in the day
/*
* Returns the number of hours in the day taking into account
* day light savings time.  This function is applicable to the
* common schedule for daylight savings used in North America
* which starts on the first Sunday in April and ends on the
* last Sunday in October.  The definition in other parts of
* the world varies and you'll have to create a different
* UDF for those areas.
*
* Example:
select dbo.udf_DT_HoursInDayNA(getdate())    [Today]
     , dbo.udf_DT_HoursInDayNA('2004-04-04') [Spring Forward]
     , dbo.udf_DT_HoursInDayNA('2004-10-31') [Fall Back]
*
* © Copyright 2004 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 the Week  Vol 2 #15 3/30/04
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN 
    
DECLARE @DSTdisabled int -- Registry value for DST disabled
      , @Year int        -- Year of OnDate
      , @Mon int         -- month of the date for @OnDate
      , @Day int         -- Day of the month for @OnDate
      , @DOW int         -- Day of the week

-- If the DOW
SELECT @Mon = MONTH(@OnDate)
     , @DOW = DATEPART(dw, @Ondate) -- Assumes @@DATEFIRST=7
     , @DAY = DAY(@OnDate)

-- Test if the date could possibly be the one we're looking for
IF (@MON !=4 and @Mon !=10)   
   OR @DOW != 1 -- Assumes @@DATEFIRST=7, for sunday
   OR (@MON=4 and @DAY >= 8) -- Couldn't be the 1st MON
   OR (@MON=10 and @Day <= 24) -- Couln't be the last SUN
   RETURN 24

-- Check the registry to be sure that the computer supports DST
    -- 1 when disabled, null when enabled.
    EXEC master..xp_regread 
                       N'HKEY_LOCAL_MACHINE'
	                 , N'System\CurrentControlSet\Control\TimeZoneInformation'
	                 , N'DisableAutoDaylightTimeSet' 
                     , @DSTdisabled OUT 
                     , N'no_output'

RETURN CASE WHEN @DSTdisabled IS NOT NULL THEN 24
            WHEN @Mon = 4 THEN 23
            WHEN @Mon = 10 THEN 25
            ELSE 24
            END
END
GO


GRANT EXEC on dbo.udf_DT_HoursInDayNA TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

For efficiency sake, the UDF first eliminates all but the Sundays that are the 1st 7 days in April or the last 7 days in October. If it's one of those days, the registry is checked to see if daylight savings is supported and then the RETURN expression decides the value to return.

Here's the example that shows results for this year:

select dbo.udf_DT_HoursInDayNA(getdate())    [Today]
     , dbo.udf_DT_HoursInDayNA('2004-04-04') [Spring Forward]
     , dbo.udf_DT_HoursInDayNA('2004-10-31') [Fall Back]
GO

(Results)
Today  Spring Forward Fall Back 
------ -------------- --------- 
    24             23        25 

This next example goes back to the practical use of this function and calculates the number of page views per second.

SET NOCOUNT ON 
GO

CREATE TABLE ViewCounts ( OnDate smalldatetime
                        , NumViews int
                         )
GO

INSERT INTO ViewCounts (OnDate, NumViews) 
                   VALUES ('2004-03-30', 1500000)
INSERT INTO ViewCounts (OnDate, NumViews) 
                   VALUES ('2004-04-04', 1500000)
INSERT INTO ViewCounts (OnDate, NumViews) 
                   VALUES ('2004-10-31', 1500000)
GO

SELECT OnDate as [Date]
   , CONVERT(float, NumViews) 
     / 3600.0 * CONVERT(float, dbo.udf_DT_HoursInDayNA(OnDate))
           [Number of Views]
   FROM ViewCounts
go

(Results - reformatted)
Date                 Number of Views  
-------------------- --------------------- 
2004-03-30 00:00:00               10000.00
2004-04-04 00:00:00                9583.33 
2004-10-31 00:00:00               10416.66

If the difference between 10000 and 9583 is important in the context of your application than as you can see it might be worth taking the number of hours in a day into account.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick