All Forums Database
shaves 22 posts Joined 04/15
29 Mar 2016
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.
 

sakthikrr 53 posts Joined 07/12
29 Mar 2016

(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

 

Sakthi

shaves 22 posts Joined 04/15
29 Mar 2016

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

You must sign in to leave a comment.