Dates during a 7 day week 1

[dvannoy]

I need to capture the dates of an entire 7 day week by just entering the first date of a week.

e.g. if I were to enter 10/11/09 then run a query, is it possible to get the results to look like the following;

10/11/09
10/12/09
10/13/09
10/14/09
10/15/09
10/16/09
10/17/09

I need all dates for a week but only want to enter the first date and then have the query return the remaining 6 days of that week for me.

any help would be appreciated.

 

[RiverGuy]

DECLARE @D DATETIME
SELECT @D = '20091011'


DECLARE @Dates TABLE (TheDate DATETIME)
DECLARE @i INT
SELECT @i = -7
WHILE @i <= 7
BEGIN
	INSERT INTO @Dates SELECT DATEADD(day, @i, @D)
	SELECT @i = @i + 1
END

SELECT * FROM @Dates WHERE DATEPART(wk, @D) = DATEPART(WK, TheDate)

 

[JeffModen]

Just another method...

--===== This could be a parameter in an inline table valued function
DECLARE @pStartDate DATETIME
 SELECT @pStartDate = '20091011'

--===== This uses the same type of magic as a Tally or Numbers table
 SELECT @pStartDate + t.Number
   FROM Master.dbo.spt_Values t --Using this as a very short tally table
  WHERE t.Type = 'P'
    AND t.Number BETWEEN 0 AND 6

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[RiverGuy]

Just as a side note...my code as posted theoretically allows the user to specify any date of a week and return all dates for that week, regardless if the date supplied is the first date.

 

[markros]

It is always a good idea to use explicit DATEADD()
See

http://blogs.lessthandot.com/index....operand-type-clash-date-is-incompatible--2008

 

[JeffModen]

True enough and excellent feature...

You can still do the same thing without having to calculate the extra week+ of dates. Because it's all done in a single select, it can still be incorporated into a high speed inline table valued function.

--===== This could be a parameter in an inline table valued function
DECLARE @pStartDate DATETIME
 SELECT @pStartDate = '20091011'

--===== This uses the same type of magic as a Tally or Numbers table
 SELECT myweek.FirstDay + t.Number
   FROM Master.dbo.spt_Values t --Using this as a very short tally table
  CROSS JOIN (SELECT DATEADD(wk,DATEDIFF(wk,0,@pStartDate),0) AS FirstDay) myweek
  WHERE t.Type = 'P'
    AND t.Number BETWEEN 0 AND 6

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Jeff,

For short fixed-length lists, doing an explicit manual SELECT statement performs better than querying a table. Also, no CROSS JOIN is needed in this case:

DECLARE @pStartDate datetime
SELECT @pStartDate = '20091012'

SELECT DaysOfWeek = DATEADD(wk,DATEDIFF(wk, 0, @pStartDate), 0) + Number
FROM (
   SELECT -1 UNION ALL SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
) X (Number)

Or, for a version that's SET DATEFIRST independent:

DECLARE @pStartDate datetime
SELECT @pStartDate = '20091012'

SELECT
	DaysOfWeek = DateAdd(dd, Number - DateDiff(dd, 0, @pStartDate + 1) % 7, @pStartDate)
FROM (
   SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) X (Number)

Note: Some of this stuff will break with the Date data type in SQL 2008. For starters, you must use DateAdd instead of adding integers to dates...

 

[JeffModen]

For short fixed-length lists, doing an explicit manual SELECT statement performs better than querying a table

Cool... can I see the test you did for that?

--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row

 

[Emtucifor]

Easy. Do a trace in SQL Profiler on SQL:BatchCompleted. Then run the following code (the only difference between these is in the derived table select numbers 0 through 6):

GO
DECLARE @pStartDate datetime
SELECT @pStartDate = '20091012'

SELECT
    DaysOfWeek = DateAdd(dd, Number - DateDiff(dd, 0, @pStartDate + 1) % 7, @pStartDate)
FROM (
   SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6
) X (Number)
GO
DECLARE @pStartDate datetime
SELECT @pStartDate = '20091012'

SELECT
    DaysOfWeek = DateAdd(dd, Number - DateDiff(dd, 0, @pStartDate + 1) % 7, @pStartDate)
FROM (
   SELECT t.Number
	FROM Master.dbo.spt_Values t
	WHERE
		t.Type = 'P'
		AND t.Number BETWEEN 0 AND 6
) X (Number)
GO

And you will see stats like the following:

[tt] CPU Reads Writes Duration
Constants 0 0 0 12
Table 31 95 0 127[/tt]
And also, look at execution plan (don't run profiler with execution plan on, it skews the results):

Constants: Constant Scan -> Compute Scalar. Subtree cost: 0.0000079
Table: Clustered Index Seek -> Computre Scalar. Subtree cost: .0033868

So it's clearly hugely superior to do a constant scan than read a table. There may be a break-even point depending on the number of rows. I just switch to a table when there are too many to reasonably type or embed in a query, since maintainability and clarity are competing values for query writing.

 

[JeffModen]

Nicely done. Once cached, they both use 0 CPU and 0 Duration and the reads drop down to just 2 logical reads for the table version, but every little bit helps especially if you're hitting the same code a lot. Thanks for the tip.


--Jeff Moden
-------------------------------------------------------------------------------
"RBAR" is pronounced "ree-bar" and is a "Modenism" for "Row By Agonizing Row