Function Date

A have initial date 20/04/2005
and a final date 25/09/2005. id like a function that return how many days
there are for each month until the final date.
Ex: 04/2005 - 10 days
05/2005 - 31 days
_
_
09/2005 - 25 days.
Is that possible, or i have to create some function that do it' Id like
some already done!
ThanksIf you don't care about wends / holidays,
SELECT DATEDIFF(DAY, '20050420', '20050925')
If you need to incorporate/ignore wends and holidays, see
http://www.aspfaq.com/2519
(I also *strongly* suggest avoiding usage and assumptions based on ambiguous
date formats like dd/mm/yyyy.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Daniel Caetano" <dcaetano@.ig.com.br> wrote in message
news:eqFrlesPFHA.244@.TK2MSFTNGP12.phx.gbl...
> A have initial date 20/04/2005
> and a final date 25/09/2005. id like a function that return how many days
> there are for each month until the final date.
> Ex: 04/2005 - 10 days
> 05/2005 - 31 days
> _
> _
> 09/2005 - 25 days.
> Is that possible, or i have to create some function that do it' Id like
> some already done!
> Thanks
>|||Try This:
Create FUNCTION dbo.MonthDays (@.StartDT Datetime, @.EndDT DateTime)
RETURNS @.Dates Table (DT DateTime, DaysInMonth SmallInt)
AS
BEGIN
Declare @.DT DateTime, @.EDT DateTime
Select @.DT = Convert(VarChar(6), @.StartDT, 112) + '01',
@.EDT = DateAdd(month, 1, @.DT)
While @.DT < @.EndDT Begin
Insert @.Dates(DT, DaysInMonth)
Values (@.DT, DateDiff(day,
Case When @.StartDT > @.DT Then @.StartDT Else @.DT END,
Case When @.EDT < @.EndDT Then @.EDT Else @.EndDT END))
Set @.DT = @.EDT
Set @.EDT = DateAdd(month, 1, @.DT)
End
RETURN
END|||create procedure GetDayNumbers(
@.StartDate datetime,@.EndDate datetime
)
as
declare @.StartDiff smallint,@.EndDiff smallint,@.Diff int
declare @.Test table (StartDate datetime,EndDate datetime)
select
@.StartDiff =
datediff(d,@.StartDate,left(convert(varch
ar(6),dateadd(m,1,@.StartDate),112),6
)+'01')-1;
select
@.EndDiff =
datediff(d,left(convert(varchar,@.EndDate
,112),6)+'01',@.EndDate)+1;
select
@.Diff =
datediff(m,@.StartDate,@.EndDate)+1
set rowcount @.Diff
select i=identity(int,1,1)
into Months
from sysobjects,syscolumns
insert @.Test
select @.StartDate StartDate,@.EndDate EndDate
select
Month=month(dateadd(m,i-1,StartDate)),
Year=year(dateadd(m,i-1,StartDate)),
NumOfDays=
case i
when 1 then @.StartDiff
when datediff(m,StartDate,EndDate)+1 then @.EndDiff
else datediff(d,dateadd(m,i-1,StartDate),dateadd(m,i,StartDate))
end
from @.Test join Months
on (datediff(m,StartDate,EndDate)+1)>=Months.i
drop table Months;
set rowcount 0
Regards,
Marko Simic
"Daniel Caetano" wrote:

> A have initial date 20/04/2005
> and a final date 25/09/2005. i′d like a function that return how many day
s
> there are for each month until the final date.
> Ex: 04/2005 - 10 days
> 05/2005 - 31 days
> _
> _
> 09/2005 - 25 days.
> Is that possible, or i have to create some function that do it' I′d like
> some already done!
> Thanks
>
>