Calculating days in a month
-
Try:
DECLARE @month int, @year int
SELECT @month = 6, @year = 2004
SELECT DATEPART(dd,DATEADD(m,1,CAST(STR(@month) + '/01/' + STR(@year) as datetime)) - 1)
You supply the month and year. The select statement turns in into a date, then adds a month giving it the first of the following month, then subtracts a day to get last day of month. There's probably more than one way to accomplish this.
Francis
-
Or given a Date:
DECLARE @DATE DATETIME
SET @DATE = GETDATE()
SELECT CAST(
-- First day of next month
DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE)+1,0)
-
-- First day of current month
DATEADD(MONTH,DATEDIFF(MONTH,0,@DATE),0)
AS INT )
/rockmoose
You must unlearn what You have learnt -
As with many things there are probably a dozen ways to answer this question and it all depends on desired input.
the formula select datediff(day,@start,@end) returns the number of days in a given period.
If you were to set @start = 2/1/2004 and @end to 3/1/2004 you would get 29
I like this way because it is really straight forward. It is easy to tell what is being returned with just a single glance.
Steve
-
I use the following function:
create function fn_NumOfDaysInMon (@date datetime)
returns int
as
begin
declare @begmonth datetime,@endmonth datetime,@days int
select @begmonth=dateadd(month,datediff(month,0,@date),0)
select @endmonth=dateadd(dd,-datepart(dd,@date),dateadd(mm,1,@date))
select @days=datediff(dd,@begmonth,@endmonth)+1
return @days
end
Use it as following:
select dbo.fn_NumOfDaysInMon('04/21/04')
-------
30
-
on second thought, a simpler way to do it is:
create function fn_NumOfDaysInMon (@date datetime)
returns int
as
begin
declare @days int
select @days=datepart(dd,dateadd(dd,-datepart(dd,@date),dateadd(mm,1,@date)))
return @days
end
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply