If that's the only field, then I'm a bit puzzled as to how you determine when you have a CostCenterID and when you have a CostCenterName. Also, you have no...

My formula

datediff(d, '19000101', @dateTo)/7 - datediff(d, '19000102', @dateFrom)/7

doesn't depend on datefirst settings and server collation. Like I have said before, it returns the number of Mondays between @dateFrom and @dateTo,...

So I've noticed - neither does mine

You could also try the script below.

I think what Sergiy suggests is that you make timeinterval (see below) a calculated and indexed column on mytable and then run the last...

I see. No, nothing's wrong. You include the second date but not the first. I thought you included both dates (like I do in my query). I apologize, your query works perfectly.

But......

What I meant was that you could easily get (-1)/7, although this wasn't clear from what I wrote.

I assumed you wanted to add 1 to the formula you stated perviously....

Sergiy, I believe the following is correct:

select case when

Datediff(dd, @startDate, @endDate - (@@Datefirst+Datepart(dw,@endDate)-2) %7) < 0

then 0 else

Datediff(dd, @startDate, @endDate - (@@Datefirst+Datepart(dw,@endDate)-2) %7) / 7 + 1

end

The division and...

Sergiy, I don't believe your formula is correct if @StartDate=@EndDate='20051231'.

Jeff, I think this modification of your formula works:

DATEDIFF(wk,@startDate,@endDate)

-CASE WHEN DATENAME(dw,@endDate)='Sunday' THEN 1 ELSE 0 END

+CASE WHEN DATENAME(dw,@startDate)='Monday' THEN 1 ELSE...

Jeff, your formula doesn't work if the start date is a Sunday - e.g if @startdate = @enddate which is a Sunday, it will return -1.

I believe the following works...

Note that the output of your function depends on the value of @@DATEFIRST:

declare @DateFirst int

select @DateFirst = @@DATEFIRST

set datefirst 1

select dbo.Monday('2006-02-13','2006-02-20')  --Returns 2

set datefirst 7

select dbo.Monday('2006-02-13','2006-02-20')  -- Returns 1 (incorrect)

set datefirst...

Something like this?

create proc countMondays

(

  @FromDate datetime,

  @ToDate datetime,

  @NumberOfMondays int output

)

as

  select @NumberOfMondays = (datediff(d, '20060213', @ToDate) + 1)/7 - datediff(d, '20060213', @FromDate)/7

go

declare @count int

exec countMondays '20000101', '20001231', @count...

I don't quite understand what you're saying. As an example, to calculate the number of Mondays in the year 2000, do as follows:

select (datediff(d, '20060213', '20001231') + 1)/7 - datediff(d,...

I think this will give the number of Mondays between the two dates (both inclusive). 20060213 can be replaced by any Monday...

select (datediff(d, '20060213', @Date2) + 1)/7 - datediff(d, '20060213', @Date1)/7

Or this one, perhaps:

select t1.Value, max(cast(t2.Value as int))

from @Table1 t1

left join @Table2 t2

on t1.Value like t2.Value + '%'

group by t1.Value

I (and JeffB, I think) assume you want the first n...

What are the definitions of the tables DataToBeParsed and OrderCatalog?