Getting the date range for the whole month

[ashab02]

Hello
I have a problem in the sense that I have a query which currently is reliant on me entering the dates for it to run manually. There are two fields I enter in a table 'CurrentStart' and 'CurrentEnd' and I then link to this table in my main query.

What I want to do is automate this process so I have no manual input but i'm having no joy on how to do this.
What I have is a report which runs every monday and reports on the previous weeks data. An example would be if I ran the report on 21/07/2008 I would want my start range to be '14/07/2008' and my end range to be '20/07/2008 23:59'.

Also if the previous weeks falls into the previous month then I just want it to go as far back as 01st of the current month. An example being if I ran my report on 07/07/2008 my start srange would be '01/07/2008' and my end range would be '06/07/2008 23:59'.
Over all it needs to look at the last seven days unless it falls into the previous month where it only needs to go as far back as 01st of the month.

I was wondering if somebody could help me and give me some help on how to do this?

 

[bborissov]

DECLARE @StartDate datetime
DECLARE @EndDate datetime
DECLARE @Current datetime

SET @Current = '20080707'

SET @StartDate = DATEADD(d, -7, @Current)
IF MONTH(@StartDate) <> MONTH(@Current)
   BEGIN
      SET @StartDate = CONVERT(varchar(6), @Current, 112)+'01'
      SET @EndDate   = DATEADD(s,-1,DATEADD(d, 6, @StartDate))
   END
ELSE
   SET @EndDate   = DATEADD(s,-1,DATEADD(d, 7, @StartDate))

SELECT @StartDate, @EndDate

NOT TESTED well.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[PWise]

What I would do is create A table (Digits) with one field (Digitid) with values from 1 till.....

and run this code

Select * 
from Sales
inner join(
			SELECT TOP 7 convert(datetime,CONVERT(varchar(10),DATEADD(d, - DigitID, GETDATE()), 101)) AS dates
			FROM Digits
			WHERE (MONTH(DATEADD(d, - DigitID, GETDATE())) = MONTH(GETDATE()))
			) SalesDates
on SalesDates.dates=Sales.SaleDate

 

[PWise]

The Above Post will always is assuming that you are always running the report on monday