SQL SERVER – UDF – User Defined Function – Get Number of Days in Month

User Defined Function 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 ENDEND   

RETURN @rtDate   

END GO  

ResultSet:
NumDaysInMonth
———————–
30

Thanks to Pinal Dave

————————————————————————————————

There is another way to get the same result. 

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

ResultSet:
NumDaysInMonth
———————–
30

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s