Umm... what about the things that start with "A"?

The answer is, what day of the week did date "0" occur on? Then, look at the (-1) and all will become clear.

Ok... no...

Grant Fritchey (11/23/2008)

---

62 is your record? You need to let your developers play more. We had one with 82 tables once. They were really shocked when it ran slowly. The...

Man, I'm with Jack... use 2 or 3 part naming in the FROM clause, add aliases, and then use the aliases everywhere else. Done properly, it makes the code...

Grant Fritchey (11/23/2008)

---

I took a couple of minutes and laid out the TSQL so it was readable (may the gods keep & protect Red Gate & SQL Prompt). It might...

Grant Fritchey (11/23/2008)

---

OK. I just read through all the posts. I can probably go with the AD phrase above because of the guys (I assume it's a guy) wretched responses....

I agree with Gail... you need to tell us "Why" you want/need to do this to make sure we give you the best solution.

Ok... the Billion row results are in. On the orignal code that Russ submitted (looks like modification of Itzek's code),

Insert Duration - 00:29:00

Convert column to NOT NULL -...

Is that true, Gail? Is there a book out there with your good name in it as a co-author?

Heh... the kinder gentler me posted on that one, thought better of it, and...

Heh... sorry, Perry... bit of an inside joke. I'll explain it sometime, but not here.

GAAAA!!!

http://www.sqlservercentral.com/Forums/Topic606992-65-1.aspx

"KISS" it...

SELECT DATEADD(wk,DATEDIFF(wk,0,somedatetimevalue+7),0)-1

You don't need to write anything to get what you want. Just run the following and some of the things it returns are things like Min, Max, and Average...