Tomm Carr (9/5/2008)

---

Why? The purpose of DATEFIRST is to allow your functions and procedures...

My solution was specifically meant to be NOT responsive to the setting of DATEFIRST.

I posted the link in one of my prior posts that has a function for the start...

That would be great. SELF JOINS get old fast.

This does the same as my last post. It just has the functions calls for SundaysInMonth nested.

select

[Year]= year(a.DT),

[Month] = month(a.DT),

SundaysInMonth =

(datediff(dd,

dateadd(dd,((datediff(dd,-53684,dateadd(mm,datediff(mm,0,a.DT),0)+6)/7)*7),-53684),

dateadd(dd,((datediff(dd,-53684,dateadd(mm,datediff(mm,-1,a.DT),-1))/7)*7),-53684)

)/7)+1

from

( -- Test dates, all months for 2008

select...

This shows a different method that only requires a single select statement.

select

[Year]= year(FirstSunday),

[Month] = month(FirstSunday),

SundaysInMonth = (datediff(dd,FirstSunday,LastSunday)/7)+1

from

(

select

*,

FirstSunday =

-- First Sunday of the Month

dateadd(dd,((datediff(dd,-53684,aa.Day7)/7)*7),-53684),

LastSunday =

-- Last Sunday of the Month

dateadd(dd,((datediff(dd,-53684,aa.EOM)/7)*7),-53684)

from

(

select

Day7...

The basic algorithm is to find the difference in days from the earliest...

Make sure the tables you are looking at have a clustered index. If there is no clustered index, the table is a heap, and the data itself cannot be...

When you tried...

The way you have your query written, you will miss any rows that fall in the last second of the day.

When you query for a date range, it is better...

You can use the function on this link for what you want to do.

Date Table Function F_TABLE_DATE

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

ss1011 (9/2/2008)

---

Thanks everyone for the feedback.

...This is why we recommend cold backups. We also recommend a scheduler to start/stop our default instance. But I suppose I can look into an...

This should do what you want

select

Weekending =

-- Find end of week date for week Sun-Sat

dateadd(dd,((datediff(dd,-53684,a.salesdate)/7)*7)+6,-53684) ,

a.line,

sum(a.cost)

from

#DAILY a

group by

dateadd(dd,((datediff(dd,-53684,a.salesdate)/7)*7)+6,-53684) ,

a.line

order by

dateadd(dd,((datediff(dd,-53684,a.salesdate)/7)*7)+6,-53684) ,

a.line

Use REPLACE to remove the /

DBCC INDEXDEFRAG stored procedure

http://weblogs.sqlteam.com/tarad/archive/2004/07/30/1834.aspx

You need to look at the type of paging to know if there is a problem.

Running backups can cause paging as the OS allocates file system cache, and this is...