list of dates - 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

General SQL Server Forums

New to SQL Server Programming

list of dates

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2008-11-11 : 17:01:55

how would I get a list of dates say between the getdate() and getdate()-20?

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-11 : 17:07:52

[code]
;with listdate ([date],i) as (
select getdate() as [date], 0 as i
union all
select dateadd(day, i, [date]),i-1 from listdate
where i > -20
)
select convert(varchar(10), [date],101) as [date] from listdate
where i < 0[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 22:46:52

[code]SELECT DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-1 * number) AS Date
FROM master..spt_values
WHERE type='p'
AND number<=20
order by Date DESC

output
--------------------------------

2008-11-12 00:00:00.000
2008-11-11 00:00:00.000
2008-11-10 00:00:00.000
2008-11-09 00:00:00.000
2008-11-08 00:00:00.000
2008-11-07 00:00:00.000
2008-11-06 00:00:00.000
2008-11-05 00:00:00.000
2008-11-04 00:00:00.000
2008-11-03 00:00:00.000
2008-11-02 00:00:00.000
2008-11-01 00:00:00.000
2008-10-31 00:00:00.000
2008-10-30 00:00:00.000
2008-10-29 00:00:00.000
2008-10-28 00:00:00.000
2008-10-27 00:00:00.000
2008-10-26 00:00:00.000
2008-10-25 00:00:00.000
2008-10-24 00:00:00.000
2008-10-23 00:00:00.000[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 22:47:56

quote:

Originally posted by hanbingl


;with listdate ([date],i) as (
select getdate() as [date], 0 as i
union all
select dateadd(day, i, [date]),i-1 from listdate
where i > -20
)
select convert(varchar(10), [date],101) as [date] from listdate
where i < 0

hanbingl this is not returning correct results

output
----------------
11/12/2008
11/11/2008
11/09/2008
11/06/2008
11/02/2008
10/28/2008
10/22/2008
10/15/2008
10/07/2008
09/28/2008
09/18/2008
09/07/2008
08/26/2008
08/13/2008
07/30/2008
07/15/2008
06/29/2008
06/12/2008
05/25/2008
05/06/2008

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-11 : 22:52:41

this is enough

;with listdate ([date],i) as (
select dateadd(dd,datediff(dd,0,getdate()),0) as [date], 1 as i
union all
select dateadd(day, -1 , [date]),i+1 from listdate
where i<=20
)
select date as [dispdate] from listdate
order by date desc

output
----------------------------
2008-11-12 00:00:00.000
2008-11-11 00:00:00.000
2008-11-10 00:00:00.000
2008-11-09 00:00:00.000
2008-11-08 00:00:00.000
2008-11-07 00:00:00.000
2008-11-06 00:00:00.000
2008-11-05 00:00:00.000
2008-11-04 00:00:00.000
2008-11-03 00:00:00.000
2008-11-02 00:00:00.000
2008-11-01 00:00:00.000
2008-10-31 00:00:00.000
2008-10-30 00:00:00.000
2008-10-29 00:00:00.000
2008-10-28 00:00:00.000
2008-10-27 00:00:00.000
2008-10-26 00:00:00.000
2008-10-25 00:00:00.000
2008-10-24 00:00:00.000
2008-10-23 00:00:00.000

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-12 : 01:44:10

quote:

Originally posted by hanbingl


;with listdate ([date],i) as (
select getdate() as [date], 0 as i
union all
select dateadd(day, i, [date]),i-1 from listdate
where i > -20
)
select convert(varchar(10), [date],101) as [date] from listdate
where i < 0

Also dont convert dates to varchars

Madhivanan

Failing to plan is Planning to fail

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-11-12 : 10:28:22

Thanks for the fix visakh16

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-12 : 10:31:48

quote:
Originally posted by hanbingl

Thanks for the fix visakh16

No problem

You're welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-13 : 01:42:44

And you dont need extra column i


;with listdate ([date]) as 
(
	select dateadd(dd,datediff(dd,0,getdate()),0) as [date]
	union all
	select dateadd(day, -1 , [date]) from listdate
	where dateadd(day, -1 , [date])+20>=dateadd(dd,datediff(dd,0,getdate()),0)
)
select date as [dispdate] from listdate
order by date desc

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 01:46:07

quote:

Originally posted by madhivanan

And you dont need extra column i


;with listdate ([date]) as 
(
	select dateadd(dd,datediff(dd,0,getdate()),0) as [date]
	union all
	select dateadd(day, -1 , [date]) from listdate
	where dateadd(day, -1 , [date])+20>=dateadd(dd,datediff(dd,0,getdate()),0)
)
select date as [dispdate] from listdate
order by date desc

Madhivanan

Failing to plan is Planning to fail

yup...i just copy pasted it from prev code anf forgot to remove it

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2008-11-17 : 06:11:24

declare @t table (date Datetime)
declare @i int
set @i = 1
while (@i<=20)
begin
insert into @t
select getdate() -@i
select @i = @i +1
end
select * from @t
d
Dates
--------------
2008-11-16 16:46:08.520
2008-11-15 16:46:08.520
2008-11-14 16:46:08.520
2008-11-13 16:46:08.520
2008-11-12 16:46:08.520
2008-11-11 16:46:08.520
2008-11-10 16:46:08.520
2008-11-09 16:46:08.520
2008-11-08 16:46:08.520
2008-11-07 16:46:08.520
2008-11-06 16:46:08.520
2008-11-05 16:46:08.520
2008-11-04 16:46:08.520
2008-11-03 16:46:08.520
2008-11-02 16:46:08.520
2008-11-01 16:46:08.520
2008-10-31 16:46:08.520
2008-10-30 16:46:08.520
2008-10-29 16:46:08.520
2008-10-28 16:46:08.520

I Struggle For Excellence

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-17 : 07:20:33

quote:
Originally posted by Nageswar9

declare @t table (date Datetime)
declare @i int
set @i = 1
while (@i<=20)
begin
insert into @t
select getdate() -@i
select @i = @i +1
end
select * from @t
d
Dates
--------------
2008-11-16 16:46:08.520
2008-11-15 16:46:08.520
2008-11-14 16:46:08.520
2008-11-13 16:46:08.520
2008-11-12 16:46:08.520
2008-11-11 16:46:08.520
2008-11-10 16:46:08.520
2008-11-09 16:46:08.520
2008-11-08 16:46:08.520
2008-11-07 16:46:08.520
2008-11-06 16:46:08.520
2008-11-05 16:46:08.520
2008-11-04 16:46:08.520
2008-11-03 16:46:08.520
2008-11-02 16:46:08.520
2008-11-01 16:46:08.520
2008-10-31 16:46:08.520
2008-10-30 16:46:08.520
2008-10-29 16:46:08.520
2008-10-28 16:46:08.520

I Struggle For Excellence

To include today's date too

declare @t table (date Datetime)
declare @i int
set @i = 0
while (@i<20)
begin
insert into @t
select getdate() -@i
select @i = @i +1
end
select * from @t

Madhivanan

Failing to plan is Planning to fail

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-11-17 : 08:05:06

--------------------------------------------------------------------------------
Originally posted by Nageswar9

declare @t table (date Datetime)
declare @i int
set @i = 1
while (@i<=20)
begin
insert into @t
select getdate() -@i
select @i = @i +1
end
select * from @t
d
Dates
--------------
2008-11-16 16:46:08.520
2008-11-15 16:46:08.520
2008-11-14 16:46:08.520
2008-11-13 16:46:08.520
2008-11-12 16:46:08.520
2008-11-11 16:46:08.520
2008-11-10 16:46:08.520
2008-11-09 16:46:08.520
2008-11-08 16:46:08.520
2008-11-07 16:46:08.520
2008-11-06 16:46:08.520
2008-11-05 16:46:08.520
2008-11-04 16:46:08.520
2008-11-03 16:46:08.520
2008-11-02 16:46:08.520
2008-11-01 16:46:08.520
2008-10-31 16:46:08.520
2008-10-30 16:46:08.520
2008-10-29 16:46:08.520
2008-10-28 16:46:08.520

I Struggle For Excellence

--------------------------------------------------------------------------------

To include today's date too

declare @t table (date Datetime)
declare @i int
set @i = 0
while (@i<20)
begin
insert into @t
select getdate() -@i
select @i = @i +1
end
select * from @t

Madhivanan

Failing to plan is Planning to fail

hai madhivanan,

then set @i value to 0 then u will get today's date also

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-11-17 : 08:08:33

<<
hai madhivanan,

then set @i value to 0 then u will get today's date also
>>

Thats what I did. See my previous reply

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources