Vryjdfkk

Today I have a problem with splitting a date range so it becomes two separate records.

Here is an example

----------------------------------------------------------------

| Record id | date_from               | date_to                |

----------------------------------------------------------------

|     A     | 2017-02-03 08:00:00.000 | 2017-02-04 17:00:00.000|

----------------------------------------------------------------

For the result I want this

----------------------------------------------------------------

| Record id | date_from               | date_to                |

----------------------------------------------------------------

|     A     | 2017-02-03 08:00:00.000 | 2017-02-03 23:59:59.000|

----------------------------------------------------------------

|     A     | 2017-02-04 00:00:00.000 | 2017-02-04 17:00:00.000|

----------------------------------------------------------------

Is there anything that can enlighten me with this problem? Thank you very much for your advice.

PS: this is dynamic and there is no limit in duration. If the "from" day is 2017-02-02 at 17:00, and the "to" day is 2017-02-04 17:00 then there will be three records, one of which is date range from 2017-02-03 00:00:00 until 2017-02-03 23:59:59.

For midnight, I guess it is based on the date time default. For the real problem, I have this table:

As you can see, from the detail for seeding mailbox databse, the range is like one day as assumption, so I guess it is more like that example :)

PS: I am using SQL Server 2014.

One way to do it is to use a table of numbers and CROSS APPLY.

Sample data

DECLARE @T TABLE (RecordID int, date_from datetime2(0), date_to datetime2(0));



INSERT INTO @T (RecordID, date_from, date_to) VALUES

(1, '2017-02-03 08:00:00' , '2017-02-04 17:00:00'),

(2, '2017-02-05 08:00:00' , '2017-02-05 17:00:00'),

(3, '2017-02-06 08:00:00' , '2017-02-10 17:00:00');

Query

In this example I generated a table of 10 numbers on the fly (CTE_Numbers). In production I have a permanent table with 100K numbers.

WITH 

CTE_Numbers1(n)

AS

(

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 

    SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

)

,CTE_Numbers

AS

(

    SELECT ROW_NUMBER() OVER (ORDER BY n) AS Number

    FROM CTE_Numbers1

)

SELECT

    T.RecordID

    ,CASE WHEN CA.Number0 = 0

    THEN date_from

    ELSE DATEADD(day, CA.Number0, CAST(T.date_from as date))

    END AS new_date_from

    ,CASE WHEN CA.Number0 = DATEDIFF(day, T.date_from, T.date_to)

    THEN date_to

    ELSE DATEADD(day, CA.Number0 + 1, CAST(T.date_from as date))

    END AS new_date_to

FROM

    @T AS T

    CROSS APPLY

    (

        SELECT CTE_Numbers.Number - 1 AS Number0

        FROM CTE_Numbers

        WHERE CTE_Numbers.Number <= DATEDIFF(day, T.date_from, T.date_to) + 1

    ) AS CA

ORDER BY

    RecordID

    ,new_date_from

;

Result

+----------+---------------------+---------------------+

| RecordID |    new_date_from    |     new_date_to     |

+----------+---------------------+---------------------+

|        1 | 2017-02-03 08:00:00 | 2017-02-04 00:00:00 |

|        1 | 2017-02-04 00:00:00 | 2017-02-04 17:00:00 |

|        2 | 2017-02-05 08:00:00 | 2017-02-05 17:00:00 |

|        3 | 2017-02-06 08:00:00 | 2017-02-07 00:00:00 |

|        3 | 2017-02-07 00:00:00 | 2017-02-08 00:00:00 |

|        3 | 2017-02-08 00:00:00 | 2017-02-09 00:00:00 |

|        3 | 2017-02-09 00:00:00 | 2017-02-10 00:00:00 |

|        3 | 2017-02-10 00:00:00 | 2017-02-10 17:00:00 |

+----------+---------------------+---------------------+

What would the difference be, if i wanted to have the first and last day of the year for a date range of 3 years, 3 times

StartDate = 1/1/2017
End Date = 12/31/2020

Looking for a result of :
201701201712
201801201812
201901201912

Thanks in advance