6

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:

enter image description here

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.

5

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 |
+----------+---------------------+---------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.