Dynamic Date Temp Table - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dynamic Date Temp Table

Author

Topic

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-09-10 : 12:45:48

Anyone have any suggestions on how to create a temp table that contains a daily date going back 7 years? Each day that goes by, the query runs, updating the temp table with the new date.
Yes, it needs to be a temp table... I don't want to create a live table.

Columns would be

fullformat = 2012-09-08 00:00:00.000
stripformat = 09/08/2012

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 12:50:01

select dateadd(yy,datediff(yy,0,getdate())-7,0),convert(varchar(11),dateadd(yy,datediff(yy,0,getdate())-7,0),103)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-09-10 : 12:55:54

Thanks Visa, but how would I populate the temp table with dates going back 7 years? I was thinking cursor....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-10 : 13:01:20

quote:
Originally posted by qman

Thanks Visa, but how would I populate the temp table with dates going back 7 years? I was thinking cursor....

put it in insert


insert #TempTable (fullformat,stripformat)
select dateadd(yy,datediff(yy,0,getdate())-7,0),
convert(varchar(11),dateadd(yy,datediff(yy,0,getdate())-7,0),103)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-09-10 : 13:07:10

This does what I am trying to accomplish.... Do you see anything wrong with this approach?

DECLARE @dateFrom AS DATETIME = convert(varchar(11),dateadd(yy,datediff(yy,0,getdate())-7,0),103)
DECLARE @dateTo AS DATETIME = getDate()

CREATE TABLE #tmpDate(DateVal VARCHAR(10));

WHILE @dateFrom < @dateTo
BEGIN
INSERT INTO #tmpDate VALUES (CONVERT(VARCHAR(10), @dateFrom , 121))
SET @dateFrom = DATEADD(DAY, 1, @dateFrom)
END
SELECT * FROM #tmpDate
DROP TABLE #tmpDate

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-09-10 : 13:08:22

You don't need a temp table for this, a view can generate all the dates:

CREATE VIEW Past7Years (fullformat,stripformat) AS
WITH n(n) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
	FROM master..spt_values a INNER JOIN master..spt_values b ON a.type=B.type
	WHERE a.type='P' AND a.number<=3)
, today(today) AS (SELECT DATEADD(DAY,DATEDIFF(DAY,0,GETDATE()),0))
, dates(DATE) AS (SELECT DATEADD(DAY,-n,today)
	FROM today CROSS JOIN n
	WHERE DATEADD(DAY,-n,today)>=DATEADD(YEAR,-7,today))
SELECT CONVERT(CHAR(23),DATE,121) fullformat, CONVERT(CHAR(10),DATE,101) stripformat FROM dates
GO

You would then do:

SELECT * FROM Past7Years

There's nothing to update or insert, it always includes from the current date to 7 years prior.

qman
Constraint Violating Yak Guru

442 Posts

Posted - 2012-09-10 : 13:35:12

Thanks all...!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources