Entering sequential date in table

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

General SQL Server Forums

New to SQL Server Programming

Entering sequential date in table

Author

Topic

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-07-12 : 18:43:40

I was using this code in my procedure:


INSERT @dates (sdate)
SELECT dateadd(day, number, @Loc_START_DATE)
FROM   master..spt_values
WHERE  type = 'P'
AND    number < datediff(day, @Loc_START_DATE, @Loc_END_DATE + 1)

The problem - when I select a start date of 1-Jan-1986 and end date as 31-May-2010, It inserts dates from 1986 to August 10, 1991 in the table.
Something to do with the master..spt_values.

Any idea, what else I can use? Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-12 : 19:52:42

yes. the number of rows required to generate the dates is more than what is in spt_values for type = 'P'. That's the limitation of using spt_values

use this F_TABLE_DATE instead

KH
[spoiler]Time is always against us[/spoiler]

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-07-12 : 19:52:45

SELECT COUNT(*) FROM master..spt_values

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Sachin.Nand

2937 Posts

Posted - 2010-07-13 : 02:54:23

How about this one?


declare @tbl as table(num int identity )
insert  @tbl  default values
while  SCOPE_IDENTITY()<1000
insert  @tbl  default values

select * from
(
	select 
	convert(datetime,convert(varchar(25),dateadd(dd,num,yourstartdate),101))as date 
	from @tbl

)t where date between yourstartdate and yourenddate

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 04:38:52

Slight modification from Idera's solution


declare @tbl as table(num int identity(0,1) ) -- starts the numbering from 0
insert  @tbl  default values
while  SCOPE_IDENTITY() < datediff(day, yourstartdate , yourenddate)
insert  @tbl  default values

select 	min(date), max(date)
from
(
	select convert(datetime,convert(varchar(25),dateadd(dd,num,yourstartdate ),101))as date 
	from   @tbl

)t where date between yourstartdate  and yourenddate

KH
[spoiler]Time is always against us[/spoiler]

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-07-13 : 11:24:58

Tara >> SELECT COUNT(*) FROM master..spt_values will give me the total count. Not sure how I can use this.

Idera and Khtan >> my sdate column in the @dates table is datetime type, yours one is identity. I can use the num values to insert into my @dates table, but thats some extra processing isn't it. How can I get the sequencial dates directly into my table.

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-13 : 11:38:34

quote:
Tara >> SELECT COUNT(*) FROM master..spt_values will give me the total count. Not sure how I can use this.

It is explaining why spt_values does not work for you. See my 1st reply

quote:
Idera and Khtan >> my sdate column in the @dates table is datetime type, yours one is identity. I can use the num values to insert into my @dates table, but thats some extra processing isn't it. How can I get the sequencial dates directly into my table.

The solution i posted did not use any identity. It is using a table function F_TABLE_DATE. You can get it from the link


INSERT @dates (sdate)
SELECT DATE
FROM   F_TABLE_DATE('1986-01-01', '2010-05-01')

KH
[spoiler]Time is always against us[/spoiler]

sqlbug
Posting Yak Master

201 Posts

Posted - 2010-07-13 : 12:40:49

Thank you all.

maeenul
Starting Member

20 Posts

Posted - 2011-05-20 : 14:08:43

Just posting this for future reference. You can use a row_number approach for generating date sequence. See the following post.

http://blog.programmingsolution.net/sql-server-2008/integer-sequence-generator-date-sequence-generator/

-----------------------
maeenul
http://www.programmingsolution.net/sqlserver2005/sqlserver-solutions/
http://sqlservertipsntricks.blogspot.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources