Just padding my posts here, but I really don't see how answer ratings would contribute much. This is a discussion board, so all the meat is in the discussion....

Maybe you could run the backup set query that was posted above and know for sure.

You have changed the requirements of what you want with each post.

You should start by thinking through what you actually want, and then asking that question, instead of asking 4...

That is not true; they can have both.

You can look at the logic inside the Date Table Function F_TABLE_DATE to see how it does it, since it generates the dates at run time.

You could use a function to generate the dates for the range you need, or you could pre-populate a permanant table with the dates you need, and select the range...

john.arnott (3/4/2009)

---

Mangal,

You've given an interesting solution that apparently depends in several ways on internals of MSSQL. Your -1 operand in the datediff could have been the slightly less obscure...

Chris Morris (3/4/2009)

---

Read about the tally (numbers) table here[/url]....

You are adding 10,000,000 rows a day, about 116 rows per second, so to keep up you will have to be able to delete at at least that rate.

You should...

Chris Morris (3/3/2009)

---

Michael, you might want to check your code, too...

DROP TABLE #Dates

CREATE TABLE #Dates (aDate DATETIME)

INSERT INTO #Dates

SELECT TOP 100 DATEADD(dd,DATEDIFF(dd, 0, getdate()),50-number)

FROM Numbers

SELECT aDate, SergiyStart,...

It would be better to run the deletes just before you do the daily defrag and statistics update.

If it only takes a 30 seconds to delete each batch of 5000,...

Did you look that up in SQL Server 2008 Books Online to confirm that statement it is true?

Did you do any testing to confirm this?

Lynn Pettis (3/3/2009)

---

Here is another way to accomplish this, based again an Sergiy's original code:

declare @pDate datetime;

set @pDate = getdate();

select

dateadd(wk, datediff(wk, 0, @pDate - 1), 0)...

karthikeyan (3/3/2009)

---

The below query will give the current week's first date and lastdate.

Declare @InputDate Datetime

select @InputDate = '28/feb/2009'

select case when upper(datename(dw,@InputDate)) = 'MONDAY' then @InputDate

...

Hard to imagine why, but it seems to be common enough.

It's dead, Jim

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