CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Faris Fajar
Faris Fajar

Reputation: 121

Split range of date and time become separate record

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 :)

Upvotes: 0

Views: 98

Answers (2)

Rajesh-Systematix
Rajesh-Systematix

Reputation: 109

You can also try like:

DECLARE @StartDate DATETIME='2017-02-04 17:00:00.000',
        @EndDate DATETIME = '2017-02-05 17:00:00.000'
SELECT DateAdd(DAY, nbr - 1, @StartDate) AS date_from,
       DateAdd(DAY, nbr - 1, GetDate()) AS date_to
FROM
  (SELECT ROW_NUMBER() OVER (
                             ORDER BY c.object_id) AS Nbr
   FROM sys.columns c ) nbrs
WHERE nbr - 1 <= DATEDIFF(DAY, @StartDate, @EndDate)

Upvotes: 0

Eric
Eric

Reputation: 5733

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

https://data.stackexchange.com/stackoverflow/query/652790

Upvotes: 1

Related Questions