today i have some problem with splitting two date with their time who have range become separate record
in order to do that, for example i have this sample
----------------------------------------------------------------
| Record id | date_from | date_to |
----------------------------------------------------------------
| A | 2017-02-03 08:00:00.000 | 2017-02-04 17:00:00.000|
----------------------------------------------------------------
then with script or procedure, i want get this result as expected below
----------------------------------------------------------------
| 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|
----------------------------------------------------------------
how to get this done by the way? and it's dynamic. thank you very much :)
It can be done by building a tally date table then join with your table data
;WITH data AS
(
SELECT * FROM (VALUES
('A', '2017-02-03 08:00', '2017-02-04 17:00'),
('B', '2017-02-05 08:00', '2017-02-09 17:00')
) t(RecordId, date_from, date_to)
)
,tally AS
(
-- It just build on the fly,
-- actually you can prebuild the table somewhere with DateValue as a key
SELECT MIN(CAST(date_from AS date)) AS DateValue, MAX(CAST(date_to AS date)) MaxDate FROM data
UNION ALL SELECT DATEADD(DAY, 1, DateValue), MaxDate FROM tally WHERE DateValue < MaxDate
)
SELECT
RecordId,
CASE
WHEN CAST(date_from AS date) = DateValue THEN date_from
ELSE CAST(DateValue AS datetime)
END AS date_from,
CASE
WHEN CAST(date_to AS date) = DateValue THEN date_to
ELSE DATEADD(SECOND, -1, DATEADD(DAY, 1, CAST(DateValue AS datetime)))
END AS date_to
FROM
-- The joining condition creates the product effect for date range into separate dates
data INNER JOIN tally ON DateValue BETWEEN data.date_from AND data.date_to