Mobile Management For SQL Server(s!) - Siccolo - SQL Management Tool
Main
How to Find Number of Days in a Month
 
	drop function get_number_of_days_in_month
	go


	create function get_number_of_days_in_month (@date datetime) returns int
	as
	begin
	
	-- select check_services.dbo.get_number_of_days_in_month (getdate())

	declare @first_of_month datetime
	declare @last_of_month datetime
	declare @number_of_days int

	set @first_of_month = convert(datetime, convert(varchar,datepart(m,@date)) + 
							'/01/' + 
						convert(varchar,datepart(yyyy,@date) )
					)
	-- first of next month...
	set @last_of_month = dateadd(m, 1, @first_of_month)
	-- ...minus one day
	set @last_of_month = dateadd(d, -1, @last_of_month)

	set @number_of_days = abs(datediff(d, @first_of_month, @last_of_month))

	return @number_of_days

	end
	
 
Or:
        drop function get_number_of_days_in_month
	go

	create function get_number_of_days_in_month (@month int, @year int) returns int
	as
	begin
		-- select dbo.get_number_of_days_in_month (1,2007)
	declare @first_of_month datetime
	declare @last_of_month datetime
	declare @number_of_days int

	set @first_of_month = convert(datetime, convert(varchar,@month) + 
							'/01/' + 
						convert(varchar,@year )
					)
	-- first of next month...
	set @last_of_month = dateadd(m, 1, @first_of_month)
	-- ...minus one day
	set @last_of_month = dateadd(d, -1, @last_of_month)

	set @number_of_days = abs(datediff(d, @first_of_month, @last_of_month))

	return @number_of_days

	end
For more examples on how to use SQL Server Date functions, DateAdd(), DateDiff(), DatePart(), GetDate() see other Siccolo articles:
- How to Find Last Day of a Month
- How to Find Beginning and End of Week

Side Note:
DateAdd() - Returns a new datetime value based on adding an interval to the specified date.
Where first parameter specifies on which part of the date to return a new value: yy(yyyy) for Year, mm(m) for Month, dd(d) for Day etc.
For example: select dateadd(d,2,getdate()) - adds 2 days to current date and returns new date.

DateDiff() - Returns the number of date and time boundaries crossed between two specified dates.
Where DateDiff( <interval>, <start date>, <end date>): <Start Date> is subtracted from <End Date>. If <Start Date> is later than <End Date>, a negative value is returned.

DatePart() - Returns an integer representing the specified datepart of the specified date.
Where DatePart( <interval>, <Date>)

    Where the datepart interval parameter is one of the following:
  • year,
  • quarter,
  • month,
  • dayofyear,
  • day,
  • week,
  • hour,
  • minute,
  • second or
  • millisecond.
The number datepart interval parameter is an integer value for the number of dateparts to be added to or subtracted from the date parameter. Now depending on your needs, your application might use this function to perform mathematical calculations on a given date.

The DATEDIFF function is used to calculate the number of date and time boundaries crossed between two different dates. This function returns an integer value.


GetDate() - Returns the current system date and time in the Microsoft® SQL Server™ standard internal format for datetime values. SQL Server provides the GetDate() function to get UTC time. The GetDate() function returns a datetime value that represents the current UTC time.

Article keywords: create function, dateadd(), datediff(), drop function, getdate(), convert(), datetime


Back To Articles Page

Free Mobile Management For SQL Server(s!) - Siccolo - SQL Management ToolQuestions? Suggestions? Concerns? - email me to siccolo_mobile_management@yahoo.com    Greg Dubinovsky © 2006
or share your thoughts at Siccolo Blog

Web being sponsor - Mid-Atlantic Processing. Well being sponsor - Clarity MediSpa. Hairless sponsor - Clarity MediSpa Laser Hair Removal.