none
Need a Table Value Function to split dateTime into days!

    Question

  • Hi
    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)

    Result :
    07/09/2013
    07/10/2013
    07/11/2013
    07/12/2013
    07/13/2013

    Can anybody help me how to accomplish this ?
    thanks in advance

    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

    Monday, July 08, 2013 9:03 PM

Answers

All replies

  • 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.

    Monday, July 08, 2013 9:09 PM
  • Hi

    i found this thread which solved my problem.

    thank you very much.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

    Monday, July 08, 2013 9:16 PM
  • declare @dt1 datetime='1/1/2013', @dt2 datetime='1/5/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 * from mycte

    Monday, July 08, 2013 9:17 PM
    Moderator
  • Thanks can u help me to returns this result as a collection of columns instead of rows ? thanks in advance

    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

    Monday, July 08, 2013 9:45 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
    */

    Monday, July 08, 2013 9:49 PM
    Moderator
  • Thanks Jingyang Li

    but your code does not returns days as a list of columns instead of rows.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

    Monday, July 08, 2013 10:10 PM
  • Thanks Jingyang Li

    but your code does not returns days as a list of columns instead of rows.


    http://www.codeproject.com/KB/codegen/DatabaseHelper.aspx

    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".

    David


    David http://blogs.msdn.com/b/dbrowne/

    Monday, July 08, 2013 10:47 PM