Create list of dates

Database
N/A

Create list of dates

select calendar_date as DATEO from sys_calendar.CALENDAR

where calendar_date between ?  and cast(? as date)+31

order by calendar_date

I inherited a report with a 2 date prompts.  The user selects the first date from a calendar on the report.  The user then selects one of the dates in the list created by the query above.  If the user selects 3/1/2016 in the first prompt, the following list is generated:

3/1/2016 12:00:00 AM

3/2/2016 12:00:00 AM

3/3/2016 12:00:00 AM

......

3/31/2016 12:00:00 AM

4/1/2016 12:00:00 AM

I have 2 questions: 

(1)    Is there a better query to use to generate this list

(2)    Is there a way to format this list so only the dates appear, e.g., 3/31/2016, 4/1/2016.  I'd like to remove the 12:00:00 AM on every line.

Thanks for your help.

2 REPLIES

Re: Create list of dates

(1)    Is there a better query to use to generate this list

Sakthi: To generate next consecutive 31 days your query is better enough

(2)    Is there a way to format this list so only the dates appear, e.g., 3/31/2016, 4/1/2016.  I'd like to remove the 12:00:00 AM on every line.

Sakthi: Just cast calendar_date to date as you have done in your where clause

select cast(calendar_date as date) as DATEO from sys_calendar.CALENDAR
where calendar_date between ? and cast(? as date)+31
order by calendar_date
N/A

Re: Create list of dates

Thank you.  That worked for me.  I appreciate the help