locked
Datediff to list dates instead of numerical values RRS feed

  • Question

  • Can someone kindly help me please to come up with a query which will accomplish the following:

    Currently my query is giving duration between two dates, e.g.

    select datediff (dd,borroweddate, getdate()) as duration from membership

    What I want is “duration” to be individually represented in the form of dates, i.e. if the duration is 3 days, instead of my query returning a figure of 3; I want it to return the dates represented by that 3. For example:

    select (“dates in the form of list startingfrom borroweddate”) as durationdate from membership

    Returned results will be:

    durationdate

    2012-11-11 00:00:00.000

    2012-11-12 00:00:00.000

    2012-11-13 00:00:00.000

    Hope my question makes sense. I don’t even think that this should be datediff but I hope you understand the meaning behind my question – dates to be listed from the time a certain event starts to the time it ends

    Thanks,


    Mpumelelo

    • Edited by Mpumelelo S Tuesday, November 13, 2012 3:50 PM
    Tuesday, November 13, 2012 1:21 PM

Answers

  • I see, in this case assuming you have a numbers table:

    select dateadd(day, n.Number, M.BorrowedDate) as [DurationDate]

    from Members M

    inner join Numbers n on n.Number between 0 and datediff(day, M.BorrowedDate, ISNULL(M.ReturnedDate, CURRENT_TIMESTAMP))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 13, 2012 4:25 PM
  • CREATE FUNCTION fn_dates(@from AS DATETIME, @to AS DATETIME)
      RETURNS @Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
    AS
    BEGIN
      DECLARE @rc AS INT
      SET @rc = 1

      INSERT INTO @Dates VALUES(@from)

      WHILE @from + @rc * 2 - 1 <= @to
      BEGIN
        INSERT INTO @Dates
          SELECT dt + @rc FROM @Dates

        SET @rc = @rc * 2
      END

      INSERT INTO @Dates
        SELECT dt + @rc FROM @Dates
        WHERE dt + @rc <= @to

      RETURN
    END
    GO

    SELECT dt FROM fn_dates('20120901', '20121031')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Mpumelelo S Wednesday, November 14, 2012 10:55 AM
    Tuesday, November 13, 2012 1:50 PM
    Answerer
  • try


    Create FUNCTION [dbo].[F_Multidate]
    (
     @borroweddate DATETIME,
     @borroweddateEnd DATETIME
    )
    RETURNS  varchar(max)
    AS
    BEGIN
     DECLARE   @Result varchar(max)=''
     DECLARE  @i int=0 ,
              @days int ;

     SET @days=datediff (dd,@borroweddate, @borroweddateEnd)
     while (@i<@days)
     begin
     SELECT @Result  =@Result+ '  '+ CONVERT(varchar(20),DATEADD(DD,@i,@borroweddate), 120 );
     SET @i=@i+1;
     end
     -- Return the result of the function
     RETURN  @Result

    END

    go

    select  [dbo].[F_Multidate](borroweddate,getdate()) as duration from membership


    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Mpumelelo S Wednesday, November 14, 2012 10:55 AM
    Tuesday, November 13, 2012 2:27 PM
  • Thank you to everyone who has contributed to this question. I have tested all suggested solutions and they work well in one way or the other. I have also come up with a recursive CTE (whose syntax is given below) which also works equally well.

    ;WITH CTE (BorrowerID, BorrowerTranNumber, BorrowedDurationDate) AS

       (SELECT r.BorrowerID, m.BorrowerTranNumber, m.BorrowedDate

          FROM MainLenderCentre m

          INNER JOIN Referrals r ON m.ReferralID = r.ReferralID

        UNION ALL

        SELECT r.BorrowerID, m.BorrowerTranNumber, c.BorrowedDurationDate + 1

          FROM CTE c INNER JOIN MainLenderCentre m ON c.BorrowerTranNumber = m.BorrowerTranNumber

          INNER JOIN Referrals r ON m.ReferralID = r.ReferralID

        WHERE c.BorrowedDurationDate <= ISNULL(m.ReturnedDate,GETDATE()))

               

    SELECT *

    FROM CTE

    OPTION(MAXRECURSION 0)

    Again, thank you everyone.

    Kind regards,


    Mpumelelo

    • Edited by Mpumelelo S Wednesday, November 14, 2012 10:54 AM
    • Marked as answer by Mpumelelo S Wednesday, November 14, 2012 11:09 AM
    Wednesday, November 14, 2012 10:52 AM

All replies

  • What is your SQL Server version and can you show both Borrowed Date and the date desired as shown as Duration Date?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 13, 2012 1:47 PM
  • CREATE FUNCTION fn_dates(@from AS DATETIME, @to AS DATETIME)
      RETURNS @Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY)
    AS
    BEGIN
      DECLARE @rc AS INT
      SET @rc = 1

      INSERT INTO @Dates VALUES(@from)

      WHILE @from + @rc * 2 - 1 <= @to
      BEGIN
        INSERT INTO @Dates
          SELECT dt + @rc FROM @Dates

        SET @rc = @rc * 2
      END

      INSERT INTO @Dates
        SELECT dt + @rc FROM @Dates
        WHERE dt + @rc <= @to

      RETURN
    END
    GO

    SELECT dt FROM fn_dates('20120901', '20121031')

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    • Marked as answer by Mpumelelo S Wednesday, November 14, 2012 10:55 AM
    Tuesday, November 13, 2012 1:50 PM
    Answerer
  • Thank you Naomi and Uri for responding to my question.

    Naomi – I’m using 2005 version.

    Uri - I will try your suggestion.

    Many thanks,


    Mpumelelo

    Tuesday, November 13, 2012 2:05 PM
  • try


    Create FUNCTION [dbo].[F_Multidate]
    (
     @borroweddate DATETIME,
     @borroweddateEnd DATETIME
    )
    RETURNS  varchar(max)
    AS
    BEGIN
     DECLARE   @Result varchar(max)=''
     DECLARE  @i int=0 ,
              @days int ;

     SET @days=datediff (dd,@borroweddate, @borroweddateEnd)
     while (@i<@days)
     begin
     SELECT @Result  =@Result+ '  '+ CONVERT(varchar(20),DATEADD(DD,@i,@borroweddate), 120 );
     SET @i=@i+1;
     end
     -- Return the result of the function
     RETURN  @Result

    END

    go

    select  [dbo].[F_Multidate](borroweddate,getdate()) as duration from membership


    Please click the Mark as Answer button if a post solves your problem!

    • Marked as answer by Mpumelelo S Wednesday, November 14, 2012 10:55 AM
    Tuesday, November 13, 2012 2:27 PM
  • Thank you all who have responded.

    @Jackie Shen – your suggestion is returning results in one single row as illustrated below

      2012-11-11 00:00:00  2012-11-12 00:00:00

    @Uri Dimant – your suggestion is returning exactly what I’m looking for as demonstrated in my preliminary tests.

    However, is use of functions the only possible way that I can tackle this problem? It’s only that in my organisation we have been asked to minimise the use of functions as much as we can due to perfomance issues associated with functions.

    Kind regards,


    Mpumelelo

    • Edited by Mpumelelo S Tuesday, November 13, 2012 3:31 PM
    Tuesday, November 13, 2012 3:28 PM
  • @Mpumelelo

    Don't you need one result row for one row of your  table membership?

    I did a test ,see the following results:

    CREATE TABLE [dbo].[membership](
     [borroweddate] [datetime] NULL
    ) ON [PRIMARY]

    GO

    insert into [dbo].[membership] values('2012-11-10 00:00:00.000')
    insert into [dbo].[membership] values('2012-11-11 00:00:00.000')
    insert into [dbo].[membership] values('2012-11-12 00:00:00.000')

    select  [dbo].[F_Multidate] (borroweddate,getdate())as duration from membership

    My SQL script will return the following three rows ,whcih is the same count to the count rows in your table membership:

      duration
      2012-11-10 00:00:00  2012-11-11 00:00:00  2012-11-12 00:00:00
      2012-11-11 00:00:00  2012-11-12 00:00:00
      2012-11-12 00:00:00


    Please click the Mark as Answer button if a post solves your problem!



    • Edited by Michael CS Tuesday, November 13, 2012 3:48 PM
    Tuesday, November 13, 2012 3:40 PM
  • Not quite Jackie Shen. Each date on each membership should be returned in the form of a record (row). For example, if a member has three dates under their name, there should be three rows for that member instead of one. However, like I have said, is it possible to get me something which may avoid use of a function?

    Many thanks,


    Mpumelelo

    Tuesday, November 13, 2012 3:47 PM
  • Please also answer my other question. In other words, I need to see both Borrowed Date and Duration Date to understand what do you want to get.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 13, 2012 4:08 PM
  • Sorry Naomi, I missed answering that part. What I need to see is a new column altogether which has to do with Duration Date. Borrowed Date is an anchor in a way which should be used to compute the individual rows of the Duration Date. The condition can be like:

    SELECT (“dates in the form of list startingfrom borroweddate”) AS durationdate FROM membership

    WHERE BorrowedDate <= ISNULL(ReturnedDate, GETDATE())

    Hope this makes sense.

    Thanks,


    Mpumelelo

    Tuesday, November 13, 2012 4:20 PM
  • I see, in this case assuming you have a numbers table:

    select dateadd(day, n.Number, M.BorrowedDate) as [DurationDate]

    from Members M

    inner join Numbers n on n.Number between 0 and datediff(day, M.BorrowedDate, ISNULL(M.ReturnedDate, CURRENT_TIMESTAMP))


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Tuesday, November 13, 2012 4:25 PM
  • Thank you Naomi. I will adapt your suggested query to my real life setting and hopefully I will be able to get it to work. I will come back with the results once done. Day for today is over here where I am.

    Kind regards,


    Mpumelelo

    Tuesday, November 13, 2012 4:53 PM
  • Thank you to everyone who has contributed to this question. I have tested all suggested solutions and they work well in one way or the other. I have also come up with a recursive CTE (whose syntax is given below) which also works equally well.

    ;WITH CTE (BorrowerID, BorrowerTranNumber, BorrowedDurationDate) AS

       (SELECT r.BorrowerID, m.BorrowerTranNumber, m.BorrowedDate

          FROM MainLenderCentre m

          INNER JOIN Referrals r ON m.ReferralID = r.ReferralID

        UNION ALL

        SELECT r.BorrowerID, m.BorrowerTranNumber, c.BorrowedDurationDate + 1

          FROM CTE c INNER JOIN MainLenderCentre m ON c.BorrowerTranNumber = m.BorrowerTranNumber

          INNER JOIN Referrals r ON m.ReferralID = r.ReferralID

        WHERE c.BorrowedDurationDate <= ISNULL(m.ReturnedDate,GETDATE()))

               

    SELECT *

    FROM CTE

    OPTION(MAXRECURSION 0)

    Again, thank you everyone.

    Kind regards,


    Mpumelelo

    • Edited by Mpumelelo S Wednesday, November 14, 2012 10:54 AM
    • Marked as answer by Mpumelelo S Wednesday, November 14, 2012 11:09 AM
    Wednesday, November 14, 2012 10:52 AM
  • Actually that is Multy statement table valued function  and not a scalar which hits performance. Have you tested it?

    As alternative you can have a Calendar table and filter out on date to get the data.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    MS SQL Consultants: Improves MS SQL Database Performance

    Wednesday, November 14, 2012 10:55 AM
    Answerer
  • I didn’t test performance in this case. It is because of the general understanding as presented in other literature sources as well as from our own experience in the organisation that we decided to minimise the use of functions.


    Mpumelelo

    Wednesday, November 14, 2012 11:07 AM