Display dates in a range 1

[deepsheep]

I know this has been asked a bunch of times, but I can't seem to find an answer that will work for me.

I'm trying to get all the dates in a specific range (I'm using week number, but it could be month or whatever) out of the database to use as report headers. I can use the data in the database unless I am on the current week, or there are days without data to report on.

So, I need to get the dates from Sunday to Saturday in any given week based on any given date. I won't be getting a week number, just a date and it won't always be the same day of the week.

What I did that worked to get it out of the data:

declare @mydate as datetime
set @mydate='2008-05-15'
select convert(nchar,enterdate,107) as enterdate from deliveryTable
where datepart(week, enterdate)=datepart(week,@mydate) and 
datepart(year, enterdate)=datepart(year,@mydate)
group by convert(nchar,enterdate,107)

And the problem is that there are sometimes dates missing, so it doesn't work nicely for column headers in my report.

How can I do this without referencing my data table?
(Please say there is a way! Otherwise my life will be difficult!)

Thanks!

 

[RiverGuy]

I usually join to a made up table. Something like

DECLARE @DateTable TABLE (
TheDate DATETIME NOT NULL )

DECLARE @MyDate DATETIME
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME

SELECT @MyDate = '2008-05-15'
SELECT @StartDate = DATEADD(day, - 7, @MyDate)
SELECT @EndDate = DATEADD(day, 7, @MyDate)

WHILE @StartDate < @EndDate
BEGIN
	INSERT INTO @DateTable SELECT @StartDate
	SELECT @StartDate = DATEADD(day, 1, @StartDate)
END

DELETE FROM @DateTable WHERE DATEPART(week, TheDate) <> DATEPART(week, @MyDate)

SELECT a.TheDate, b.* 
FROM @DateTable a
INNER JOIN YourTable b ON a.TheDate = b.DateColumn.....

 

[deepsheep]

Not the most elegant solution, but it will definatly work!

I've turned it to a table-valued function and intend to modify it so that I can do more that just a week and can do any date range. Here's my version:

CREATE FUNCTION [dbo].[DatesInRange] 
(
	@MyDate DATETIME,
	@datepart nchar(20)
)
RETURNS 
@DatesInRange  table
(
	TheDate DATETIME 
)
AS
BEGIN
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SELECT @StartDate = DATEADD(day, - 7, @MyDate)
SELECT @EndDate = DATEADD(day, 7, @MyDate)

WHILE @StartDate < @EndDate
BEGIN
    INSERT INTO @DatesInRange SELECT @StartDate
    SELECT @StartDate = DATEADD(day, 1, @StartDate)
END

DELETE FROM @DatesInRange WHERE DATEPART(week, TheDate) <> DATEPART(week, @MyDate)
	
	RETURN 
END

I'd like to know how other people manage the same problem.

 

[RiverGuy]

Looks good. Some developers/dba's will keep a date table in the database. This is especially true in data warehouses as a date table is almost a given to be there.