Following User Defined Function (UDF) returns the numbers of days in month. It is very simple yet very powerful and full proof UDF.
CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END
GO
Run following script in Query Editor:
SELECT dbo.udf_GetNumDaysInMonth(GETDATE()) NumDaysInMonth
GO
ResultSet:
NumDaysInMonth
———————–
31
Testing the Days in Month Function
The leap year rule in this function is the full one, and that is what makes it reliable. A year is a leap year if it can be divided by 4, except years that can be divided by 100, unless they can also be divided by 400. So 2000 was a leap year, but 1900 was not.
Whenever I write a date function, I test it with a short list of dates that cover the tricky cases:
- February 2008 should return 29, and February 2007 should return 28.
- February 2000 should return 29, and February 1900 should return 28.
- April should return 30, and December should return 31.
- Try a NULL too. This version returns 28 for NULL, because every test in the CASE fails and it falls through to the last ELSE. Add a check at the top if that matters to you.
There is also a shorter way that lets SQL Server do the calendar math. Find the last day of the month, then take its day number: DAY(DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @date) + 1, 0))). On SQL Server 2012 and later it gets even simpler with DAY(EOMONTH(@date)).
Keep in mind that a scalar function runs once for every row. For a report over a few thousand rows it is fine, but in a big query I would put the expression inline, or join to a calendar table that already stores the number of days for each month.
A calendar table is worth building once. It is a plain table with one row per date and columns for everything you ever need to know about that date: the month name, the week number, the number of days in that month, whether it is a weekend or a holiday. Queries join to it instead of calling functions, which keeps them simple and fast.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
35 Comments. Leave new
Thanks Simon,
I have used similar logic many times. I just decided to do something different. You can check other datetime related articles here.
http://blog.sqlauthority.com/tag/sql-datetime/
Regards,
Pinal Dave ( http://blog.sqlauthority.com/ )
You really don’t need to do the math, you can just get the last day of the month using built in date functions and assign that to your return variable. This is built in for you so you don’t have to worry about leap years or anything
CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = DATEPART(dd, DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,@myDateTime)+1, 0)))
RETURN @rtDate
END
GO
Hello, Expert
I’m using Transact-Query, how I select a particular month out of in different years in the database?
The format column field 2005/08/26…….
thanks you
ryan,
Hi This User defined function will return the no. of days in a month
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth](
@CurrentDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @ReturnDays INT
SET @ReturnDays =
CASE WHEN MONTH(@CurrentDate) IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@CurrentDate) IN (4, 6, 9, 11) THEN 30
ELSE
CASE WHEN (YEAR(@CurrentDate) % 4 = 0 AND YEAR(@CurrentDate) % 100 != 0) OR (YEAR(@CurrentDate) % 400 = 0) THEN 29
ELSE 28
END
END
RETURN @ReturnDays
END
–SELECT dbo.ufn_GetDaysInMonth(GETDATE()) No_Of_Days_In_Month
GO
Try this one-liner:
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth](
@CurrentDate DATETIME )
RETURNS INT
AS
BEGIN
RETURN DAY(DATEADD(d, -DAY(DATEADD(m,1,@dt)),DATEADD(m,1,@dt)))
END
Hi pinaldave,
Thank you very much
Just a modification to Doug’s posting :
CREATE FUNCTION [dbo].[ufn_GetDaysInMonth] (@CurrentDate DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @RetDate INT
SET @RetDate = DAY(DATEADD(d, -DAY(DATEADD(m,1,@CurrentDate)),DATEADD(m,1,@CurrentDate)))
RETURN @RetDate
END
— SELECT [dbo].[ufn_GetDaysInMonth] (‘2008/02/25’)
EXCELLENT …:))
EXACTLY SUITS MY REQUIREMENT
SUPERB
THNX A LOT………
We have already some built in functions avail, then why should we go behind all those steps…
Anyway, good logic…
-Mahesh
I want No.of days in a Month – Here i will give Month(2) and Year(2009),
so, there are two parameters.
Pls Give me the function
There are many ways
Here is one
declare @month int, @year int
select @month=2, @year=2009
select day(dateadd(day,-1,cast(@year*10000+(@month+1)*100+1 as char(8))))
Madhivanan
hi.. experts… i need a similar UDF…
my req is…. if i give 3 param’s to the UDF.. like..
“month,year and dayofweek” then i should get a list of that day fom the given month,year
ex. for April 2009 Sunday
UDFDays(@mm,@yy,@dyw)
i.e UDFDays(04,2009,’Sunday’)
then i should get list as..
6
13
20
27
i.e… the dates from the month given for weekday.. like for Apil 2009 Saturday..
i expect..esult as
5
12
19
26
etc…
thanks in advance…
Re post:… slight mistake frm my side…
hi.. experts… i need a similar UDF…
my req is…. if i give 3 param’s to the UDF.. like..
“month,year and dayofweek” then i should get a list of that day fom the given month,year
ex. for April 2009 Sunday
UDFDays(@mm,@yy,@dyw)
i.e UDFDays(04,2009,’Sunday’)
then i should get list as..
5
12
19
26
i.e… the dates from the month given for weekday.. like for Apil 2009 Saturday..
i expect..esult as
4
11
18
25
etc…
thanks in advance…
@gourav, here’s a quick shot:
CREATE FUNCTION UDFDays(@mm tinyint,@yy smallint,@dyw varchar(9))
RETURNS TABLE
AS
RETURN
WITH
Data(The_Date)
AS
(
SELECT CONVERT(smalldatetime, CAST(@mm as varchar(2)) + ‘/01/’ + CAST(@YY as varchar(4)) , 101)
),
Days_In_Month
AS
(
SELECT The_Date, DATEADD(d, -1, DATEADD(m, 1, The_Date)) Next_Month FROM Data
UNION ALL
SELECT DATEADD(d, 1, The_Date), Next_Month FROM Days_In_Month WHERE The_Date < Next_Month
)
SELECT
DATEPART(d, The_Date) Day_Number
FROM
Days_In_Month
WHERE
DATENAME(dw, The_Date) = @dyw;
GO
thanks bro… you hav done it….thank you very very much…
Regards
Gourav
Very Informative site….
Hi expert,
You are excelent to write this function.
Thank you very much………………..
We can write this also instead of case when if someone feels difficulty in using case statements then it will be easy for them using this…………………..
create function dbo.retdates(@date datetime)
returns int
as
begin
declare @out int
if month (@date) in(1,3,5,7,8,10,12)
set @out=31
else if month(@date) in(4,6,9,11)
set @out=30
else if ((year(@date)%4=0) and (year(@date)%100!=0)) or (year(@date)%400=0)
set @out=29
else
set @out=28
return @out
end
go
I want to subscribe
How do i find number of sundays in given month?
Hi,
what are the major difference between functions and Procedures?
@Pavan.
Major Difference between Function and Procedure.
Function should return a value where as procedure might or might not.
You cannot run select statement on procedures where as you can run a select statement on (Table) functions.
You cannot perform many T-SQL operation inside a function where as you perform many more T-SQL Operations inside a procedure.
~Peace.
Hello Friends,
This site was very informative, i have an issue, would be glad if you can help me.
The issue that i have to write a user defined funtion
format of dtActivityStartDate/dtActivityFinishDate: 2010-09-17 14:50:51.150 Note: Both dtActivityStartDate/dtActivityFinishDate
vcActivityName = Process Request
usdFuncTimeCalc (vcActivityName,dtActivityStartDate, dtActivityFinishDate)
i need to calculate time elasped for that type of activity following are the rules:
(If Process Request is the activity)
Working Days: Monday through Saturday
Hours of Operation: 9AM – 5PM
only working hours of day need to the counted like for example if it is sep 15 11 Am is dtActivityStartDate & Sep 17 is dtActivityFinishDate is 10 Am. then time elapsed is 11am to 5pm on sep 15 , 9 to 5 on sep 16 & 9 to 10 on sep 17 so total should be
6+ 8 + 1 = 15 hours + minutes.
format of date time: 2010-09-17 14:50:51.150
vcActivityName = Process Request