Generation of an array of 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 2000 Forums

Transact-SQL (2000)

Generation of an array of dates

Author

Topic

Johnyalm
Starting Member

49 Posts

Posted - 2005-11-13 : 12:37:43

I would like to generate an temporary table of 14 dates from today and 1 4 days backwards.

I will use the table as input to an list-box for a webbapplication.

I cannont do it in asp-code because I use an application where I only can work in the application layer.

So can I create a procedure that takse GETUTCDATE() and then generates the dates backwards?

www.mirrorgate.com

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-13 : 13:43:46

select dateadd(dd, ints.i * - 1, getdate())
from
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
from
(select i = 0 union select 1) as i1 ,
(select i = 0 union select 2) as i2 ,
(select i = 0 union select 4) as i3 ,
(select i = 0 union select 8) as i4 ,
(select i = 0 union select 16) as i5 ,
(select i = 0 union select 32) as i6
) ints
where ints.i between 0 and 14

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-13 : 23:47:48

hey thatz really a nice query...

Can you explain in breif behind the logic for this query .. ????

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-14 : 00:38:04

Declare @date table(d datetime)
Declare @d datetime

set @d=DateAdd(day,Datediff(day,0,getdate()),-14)

While @d<=getdate()
Begin
	Insert into @date values (@d)
	set @d=@d+1
End
Select d from @date

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-14 : 00:53:22

nr,

Awsome piece of codes

[KH]

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-14 : 13:08:23

quote:
Originally posted by chiragkhabaria

hey thatz really a nice query...

Can you explain in breif behind the logic for this query .. ????

Complicated things can be done by simple thinking

The idea is that it does a cross join between all the derived tables containing powers of 2. When it sums the values it ends up with consecutive numbers.

for
(0,1)
(0,2)

result is
0 + 0 = 0
0 + 2 = 2
1 + 0 = 1
1 + 2 = 3

and so on.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-14 : 23:37:57

aha.... Thanks Learnt somthing new and important..

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-15 : 00:42:42

Nice Logic Nigel

Madhivanan

Failing to plan is Planning to fail

Johnyalm
Starting Member

49 Posts

Posted - 2005-11-15 : 03:04:55

Thanks nr!

Great code. There is one more thing.

I am running collation swedish_finish in the application but when using this code and taking out the YYYY-MM-DD part of the datatime with LEFT then the result is presented by the server in the format 'Nov 10 2005' etc.

Is there a way to get the format '2005-11-10' instead?

www.mirrorgate.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-11-15 : 03:07:41

use convert(char(10), date, 121). Check out BOL for details

[KH]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-15 : 03:34:31

If you use Front End application, do the formation there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources