Printing Date Range as comma separated value

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)

Printing Date Range as comma separated value

Author

Topic

amitranjan
Starting Member

45 Posts

Posted - 2010-07-21 : 22:44:42

Hi ,
I need a query to generate comma separated string from a given date range. Also my date is in format yyyymmdd.

Example
StartDate = 20100722
EndDate = 20100725

output = 20100722,20100723,20100724,20100725

Also help me writing the query, since my search is based on two conditions, (i) on single date and another (ii) if date range is supplied. Right now i am using if else condition , that is

if @startdate is not null and @enddate is not null
-- use date range
else
use only startdate.

Please suggest me any optimized way.

amit Ranjan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-07-21 : 22:57:03

[code]
declare @StartDate date,
@EndDate date,
@Output varchar(max)

select @StartDate = '20100722',
@EndDate = '20100725'

while @StartDate <= @EndDate
begin
select @Output = isnull(@Output + ',', '') + convert(varchar(10), @StartDate, 112)
select @StartDate = dateadd(day, 1, @StartDate)
end

select @Output
[/code]

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

amitranjan
Starting Member

45 Posts

Posted - 2010-07-21 : 23:49:13

Thanks a lot... it was exactly what i need.

amit Ranjan

parody
Posting Yak Master

111 Posts

Posted - 2010-07-23 : 09:58:37

an alternative without loop...

DECLARE @StartDate char(8)
DECLARE @EndDate char(8)
DECLARE @TestArray varchar(max)

SET @StartDate = '20100722'
SET @EndDate = '20100725';

WITH Dates AS
(
SELECT
1 AS ID
,@StartDate AS DT
UNION ALL
SELECT
ID + 1
,convert(char(8),DATEADD(dd,1,convert(date,DT)),112)
FROM Dates
WHERE DT < @EndDate
)

SELECT @TestArray =
ISNULL(@TestArray,'')
+ CASE WHEN ID = 1 THEN '' ELSE ',' END
+ DT
FROM Dates
ORDER BY DT ASC

SELECT @TestArray

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources