How to fill a table with periodic dates

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 2005 Forums

Transact-SQL (2005)

How to fill a table with periodic dates

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-11-19 : 18:16:32

I am working on a web app that is connected to a database.
I want to find a way to fill a column with specific dates.
For example is a user enter as a date the first monday of the year I want to automatically fill the column with all the mondays of the current year.
Is that possible?
Thank you very much!

hey001us
Posting Yak Master

185 Posts

Posted - 2008-11-19 : 19:43:10

You can use the Function F_TABLE_DATE to achieve this. It’s really cool function.
Here the URL:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

hey

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 01:45:34

[code]DECLARE @FirstDate datetime
SET @FirstDate='2008-01-07'-first monday

;With Date_CTE(Date) AS
(
SELECT @FirstDate
UNION ALL
SELECT DATEADD(dd,7,Date)
FROM Date_CTE
WHERE DATEADD(dd,7,Date)<DATEADD(yy,DATEDIFF(yy,0,@FirstDate)+1,0)
)

select * from Date_CTE

OPTION (MAXRECURSION 0)[/code]

skiabox
Posting Yak Master

169 Posts

Posted - 2008-11-20 : 06:22:19

visakh16 this code produce errors.
Are you sure that the code is correct?
Thank for answering!

skiabox
Posting Yak Master

169 Posts

Posted - 2008-11-20 : 06:41:49

I removed -first monday from the code and it worked!
Thanx again!

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-11-20 : 06:48:29

there is a spt_values table that has some numbers in it (I think type='p'). You can select the 'number' column from this, multiply by 60*60*24*7 and add it to your starting date while the date is still in the same year.
Or something like that....
I do not have access to a server so this is a bit vague.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 06:53:29

quote:
Originally posted by LoztInSpace

there is a spt_values table that has some numbers in it (I think type='p'). You can select the 'number' column from this, multiply by 60*60*24*7 and add it to your starting date while the date is still in the same year.
Or something like that....
I do not have access to a server so this is a bit vague.

thats also works. but only thing is that its not advisable to use system tables like spt_values on production code. the number of record can vary based on sql server versions and so it may not always produce desired result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 06:53:48

quote:
Originally posted by skiabox

I removed -first monday from the code and it worked!
Thanx again!

you're welcome

skiabox
Posting Yak Master

169 Posts

Posted - 2008-11-20 : 09:17:22

visakh : did we created with your code any table with these values?
What I want to do is populate an existing table with these values.
Any ideas on how to do that?
Thanks again for your help.
You are an awesome developer.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-20 : 09:20:46

quote:
Originally posted by skiabox

visakh : did we created with your code any table with these values?
What I want to do is populate an existing table with these values.
Any ideas on how to do that?
Thanks again for your help.
You are an awesome developer.

Nope. we just created a temporary form of table called CTE which is just for generating number. After that it doesnt have existence. For getting numbers onto table just use like below


DECLARE @FirstDate datetime
SET @FirstDate='2008-01-07'-first monday

;With Date_CTE(Date) AS
(
SELECT @FirstDate
UNION ALL
SELECT DATEADD(dd,7,Date)
FROM Date_CTE
WHERE DATEADD(dd,7,Date)<DATEADD(yy,DATEDIFF(yy,0,@FirstDate)+1,0)
)

INSERT INTO YourCreatedTable (Namefield)
select Date from Date_CTE

OPTION (MAXRECURSION 0)

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-07 : 15:22:37

Can we make this code more flexible?
For example can we add a last day or enter manually the period?
(I believe the last thing is achieved by replacing the number 7 in the formula with a declared variable)

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-07 : 15:44:02

CREATE PROCEDURE dbo.usp_GenerateDates
@startdate DATETIME ,@enddate DATETIME ,@period int
AS
BEGIN
;WITH DatesCTE(CurrentDate) AS
(
SELECT @startdate AS CurrentDate
UNION ALL
SELECT DATEADD(day,@period,CurrentDate)
FROM DatesCTE
WHERE CurrentDate < @enddate
)

INSERT INTO MyCreatedTable (Namefield)
SELECT CurrentDate FROM DatesCTE
OPTION (MAXRECURSION 0)
END
GO

Have I figured it right?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-07 : 17:35:18

I tried the sp and it worked!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-07 : 20:45:35

Cool

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-08 : 08:03:31

Now I want to add two extra columns to the table that will have the same values in all rows of the subsequent dates.

How can I fill the rows?
(Let's suppose that the @value1 goes to column1 and @value2 goes to column2)

I started the code below but I did not do the Insert part

CREATE PROCEDURE dbo.usp_GenerateDates
@startdate DATETIME ,@enddate DATETIME ,@period int, @value1 int, @value2 int
AS
BEGIN
;WITH DatesCTE(CurrentDate) AS
(
SELECT @startdate AS CurrentDate
UNION ALL
SELECT DATEADD(day,@period,CurrentDate)
FROM DatesCTE
WHERE CurrentDate < @enddate
)

INSERT INTO MyCreatedTable (Namefield)
SELECT CurrentDate FROM DatesCTE
OPTION (MAXRECURSION 0)
END
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-08 : 08:33:40

[code]
CREATE PROCEDURE dbo.usp_GenerateDates
@startdate DATETIME ,@enddate DATETIME ,@period int, @value1 int, @value2 int
AS
BEGIN
;WITH DatesCTE(CurrentDate) AS
(
SELECT @startdate AS CurrentDate
UNION ALL
SELECT DATEADD(day,@period,CurrentDate)
FROM DatesCTE
WHERE CurrentDate < @enddate
)

INSERT INTO MyCreatedTable (Namefield,field1,field2)
SELECT CurrentDate,@value1,@value2 FROM DatesCTE
OPTION (MAXRECURSION 0)
END
GO
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources