i need a table value function to send startTime & endTime as parameters and returns a table which contains each 1 day in one seperate record. for example :
startTime : 07/09/2013
endTime : 07/13/2013
select dbo.MyFunction(startTime, endTime)
Can anybody help me how to accomplish this ?
thanks in advance
how about creating a calendar table in your database with 1 record per day.
Then you can join your queries to your calendar table on calendardate between StartTime and endTime, you won't even need a function.
- Proposed as answer by Naomi NModerator Monday, July 08, 2013 11:10 PM
declare @dt1 datetime='07/09/2013', @dt2 datetime='07/13/2013' ;with mycte as ( select 0 as n, @dt1 as startdate UNION ALL select n+1 , dateadd(day,1,startdate) enddate from mycte WHERE dateadd(day,1,startdate)<=@dt2 ) select Cast(startdate as date) as dt from mycte /* dt 2013-07-09 2013-07-10 2013-07-11 2013-07-12 2013-07-13 */
Thanks Jingyang Li
but your code does not returns days as a list of columns instead of rows.
The columns returned by a table-valued function are fixed at design-time. So you can't return the days as a "collection of columns".
- Proposed as answer by Naomi NModerator Monday, July 08, 2013 11:11 PM