I have 4 dates:
declare @.PeriodDateBegin smalldatetime
declare @.PeriodDateEnd smalldatetime
declare @.BeginDate smalldatetime
declare @.EndDate smalldatetime
Does anyone know a function that can give me the number of days that
fall in a period?
Example:
set @.PeriodDateBegin = '2005-01-01'
set @.PeriodDateEnd = '2005-01-31'
set @.BeginDate = '2005-01-16'
set @.EndDate = '2005-06-01'
The result should be 16.
The function should work quickly so I prefer not using a calendar
table, if this is possible.
Kind regards,
Stijn Verrept.Hi
Can you be more specific? Why 16?
How does four dates relate each other?
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:%23Y%23wqO9UFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I have 4 dates:
> declare @.PeriodDateBegin smalldatetime
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> Does anyone know a function that can give me the number of days that
> fall in a period?
> Example:
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> The result should be 16.
> The function should work quickly so I prefer not using a calendar
> table, if this is possible.
>
> --
> Kind regards,
> Stijn Verrept.|||Uri Dimant wrote:
> Hi
> Can you be more specific? Why 16?
> How does four dates relate each other?
Of course: you have two timespans: the one from @.PeriodDateBegin till
@.PeriodDateEnd and the one from @.BeginDate till @.EndDate. 16 is the
number of days that overlap.
I hope it is clear now.
Kind regards,
Stijn Verrept.|||Hi
A calendar table is often the fastest solution when working with
dates/periods, therefore you may not get the quickest solution without
trying that option.
John
"Stijn Verrept" <stijn@.no_s.p.a.n.entrysoft.com> wrote in message
news:%23Y%23wqO9UFHA.2520@.TK2MSFTNGP09.phx.gbl...
>I have 4 dates:
> declare @.PeriodDateBegin smalldatetime
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> Does anyone know a function that can give me the number of days that
> fall in a period?
> Example:
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> The result should be 16.
> The function should work quickly so I prefer not using a calendar
> table, if this is possible.
>
> --
> Kind regards,
> Stijn Verrept.|||declare @.PeriodDateEnd smalldatetime
declare @.BeginDate smalldatetime
declare @.EndDate smalldatetime
set @.PeriodDateBegin = '2005-01-01'
set @.PeriodDateEnd = '2005-01-31'
set @.BeginDate = '2005-01-16'
set @.EndDate = '2005-06-01'
Select DateDiff(day,
Case When @.PeriodDateBegin > @.BeginDate
Then @.PeriodDateBegin Else @.BeginDate End,
Case When @.PeriodDateEnd < @.EndDate
Then @.PeriodDateEnd Else @.EndDate End)
+ 1 -- to count days inclusively and generate 16...
"Stijn Verrept" wrote:
> Uri Dimant wrote:
>
> Of course: you have two timespans: the one from @.PeriodDateBegin till
> @.PeriodDateEnd and the one from @.BeginDate till @.EndDate. 16 is the
> number of days that overlap.
> I hope it is clear now.
>
> --
> Kind regards,
> Stijn Verrept.
>|||The + 1 is because the datediff() function actually counts date "boundaries"
between the two specified datetimes
"CBretana" wrote:
> declare @.PeriodDateEnd smalldatetime
> declare @.BeginDate smalldatetime
> declare @.EndDate smalldatetime
> set @.PeriodDateBegin = '2005-01-01'
> set @.PeriodDateEnd = '2005-01-31'
> set @.BeginDate = '2005-01-16'
> set @.EndDate = '2005-06-01'
> Select DateDiff(day,
> Case When @.PeriodDateBegin > @.BeginDate
> Then @.PeriodDateBegin Else @.BeginDate End,
> Case When @.PeriodDateEnd < @.EndDate
> Then @.PeriodDateEnd Else @.EndDate End)
> + 1 -- to count days inclusively and generate 16...
> "Stijn Verrept" wrote:
>|||CBretana wrote:
> The + 1 is because the datediff() function actually counts date
> "boundaries" between the two specified datetimes
Great thanks a lot!!! I believe this will indeed go faster than
working with calendar table.
Kind regards,
Stijn Verrept.
Showing posts with label datesdeclare. Show all posts
Showing posts with label datesdeclare. Show all posts
Tuesday, March 27, 2012
Subscribe to:
Posts (Atom)