<b>Get DateList from given 2 dates</b> 1

[happyIndian100]

Hi,

<i>I have a requirement like this.
User will input DateFrom and DateTo dates and i need to generate a datelist.
eg., user input </i>
DateFrom=21/11/2004 <i>and </i>
DateTo=15/03/2005

<i>then i need to get </i>DateList='21/11/2004,01/12/2004,01/01/2005,01/02/2005,15/03/2005'

<i>
Please help me write a StoredProcedure for this.

Thank You All in advance..</i>

Rgds
Ravi

 

[JamesLean]

Can you confirm the logic of how you determine which dates to include? Also, do you need the list returned as a comma-delimited string or can it be in a table?

--James

 

[miq]

hi,

declare @datelist varchar(8000)
select @DateList = coalesce(@datelist + ',', '') + cast(date_field_name as varchar) from Table_name
where date >= datefrom and date <= dateto
select @datelist
go

 

[happyIndian100]

Hi James,

I need the list as a comma-delimited string.

The Logic is, the first and last dates must be according to the users input i.e., FromDate and ToDate respectively.

and the rest of the dates must start with day one and month increment by one.

Eg.,FromDate = 13/11/2004
ToDate = 14/02/2005 then the final list must be,

13/11/2004,01/12/2004,01/01/2005,14/02/2005


Hope i made it clear to you.

--
Ravi

 

[JamesLean]

OK, this should work:

DECLARE @from datetime,
	@to datetime,
	@date_string varchar(1000),
	@curr_date datetime

SET @from = '20041113'
SET @to = '20050214'

SET @date_string = CONVERT(varchar(10), @from, 103)

SET @curr_date = CONVERT(varchar(6), DATEADD(mm, 1, @from), 112) + '01'

WHILE CONVERT(varchar(6), @curr_date, 112) < CONVERT(varchar(6), @to, 112)
BEGIN
	SET @date_string = @date_string + ',' + CONVERT(varchar(10), @curr_date, 103)
	SET @curr_date = DATEADD(mm, 1, @curr_date)
END

SET @date_string = @date_string + ',' + CONVERT(varchar(10), @to, 103)

PRINT @date_string

--James

 

[happyIndian100]

Hi james,

Thankx for the solution.

but can u plz suggest me if i want to use the dates in the following format:

SET @from = '13/11/2004'
SET @to = '14/02/2005'

and also if the DateFrom > DateTo i only want to take DateFrom into the list.

Thx

--
Ravi

 

[JamesLean]

The way you specify the dates won't change the output - they are datetime variables so will be stored as such. It is usually advisable to specify them in an unambiguous format as per my example but if you really want to use the UK format then make sure you put

SET DATEFORMAT dmy

at the top of your code.

As for your second requirement just add a check before concatenating the @to date:

IF @to > @from SET @date_string = @date_string + ',' + CONVERT(varchar(10), @to, 103)

--James