Thread: Getting the number of hours in a month

1. Registered User
Join Date
Jun 2003
Location
Montreal
Posts
38

Unanswered: Getting the number of hours in a month

Hi,

I'd like a function that returns the number of hours in a specific month (or the number of days which I could then multiply by 24). The function would have to consider leap years for February.

Any ideas?

Thanks,

Skip.

2. Registered User
Join Date
Mar 2004
Location
Toronto
Posts
28
Hi Skip, this calculates the number of hours in the month containing the date 20040101.

It goes 1 month forward, then 1 day back to get the last day of the month. The 24 * converts days to hours.

This does not take into account daylight savings :-(

3. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
declare @month datetime
set @month = getdate()

select datediff(hour, convert(char(7), @month, 120)+'-01', dateadd(month, 1, convert(char(7), @month, 120)+'-01'))

4. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
At least for the United States, you could use:
Code:
```CREATE FUNCTION fHoursInMonth(@pd1 DATETIME) RETURNS INT AS
BEGIN
DECLARE
@dWork		DATETIME

SET @dWork = Convert(CHAR(8), @pd1, 121) + '01'
RETURN 24 * DateDiff(day, @dWork, DateAdd(month, 1, @dWork))
+     CASE Month(@dWork)
WHEN  4 THEN -1	-- Lose an hour to "Spring forward"
WHEN 10 THEN  1	-- Gain an hour from "Fall back"
ELSE 0
END
END```
-PatP

5. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
I would think that datediff(hour....) would account for leap years and daylight savings.

6. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
Not according to:
Code:
```SELECT a.d, dbo.fHoursInMonth(a.d), DateDiff(hour, a.d, DateAdd(month, 1, a.d))
FROM (
SELECT '2004-01-15' AS d
UNION SELECT '2004-02-15'
UNION SELECT '2004-03-15'
UNION SELECT '2004-04-15'
UNION SELECT '2004-05-15'
UNION SELECT '2004-06-15'
UNION SELECT '2004-07-15'
UNION SELECT '2004-08-15'
UNION SELECT '2004-09-15'
UNION SELECT '2004-10-15'
UNION SELECT '2004-11-15'
UNION SELECT '2004-12-15') AS a```
The biggest problem is that the observance of Daylight Savings time, the dates of the changes, and even the amount of change (not everyone uses one hour) are location dependant.

-PatP

7. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595