CODEWITHSUNDEEP
sqlsql-serverdatesplit
OwenS
OwenS

Reputation: 233

SQL Split Dates

I want to split every Row of a table with StartTime and StopTime to days.

Example: 

User    Site    Title    TimeStampStart(01-11-2013)    TimeStampStop(05-11-2013)

I would like to obtain five Rows with day in the interval as Timestamp:

User    Site    Title    TimeStampStart(01-11-2013)
User    Site    Title    TimeStampStart(02-11-2013)
User    Site    Title    TimeStampStart(03-11-2013)
User    Site    Title    TimeStampStart(04-11-2013)
User    Site    Title    TimeStampStart(05-11-2013)

I have created a ListDates Function that allows me to split in days from two timestamps but I cannot use it as I need to use a table as argument.

Upvotes: 0

Views: 159

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

If you have a numbers table, this is fairly trivial.

CREATE TABLE dbo.Numbers(n INT PRIMARY KEY);

INSERT dbo.Numbers(n) SELECT TOP (1000) rn = ROW_NUMBER() OVER 
  (ORDER BY [object_id]) FROM sys.all_objects;

-- if you may have dates that may be more than 1000 days apart (~3 years), 
-- increase TOP and use a cross join against one of the other system views

Then:

SELECT s.Site, DATEADD(DAY, n.n, TimeStampStart)
FROM dbo.YourTable AS s
INNER JOIN dbo.Numbers AS n
ON n.n <= DATEDIFF(DAY, s.TimeStampStart, s.TimeStampStop);

SQLFiddle Demo

Many examples of generating sets (and why a numbers table will typically work out best):

http://sqlperformance.com/generate-a-set-1

http://sqlperformance.com/generate-a-set-2

http://sqlperformance.com/generate-a-set-3

Upvotes: 3

GarethD
GarethD

Reputation: 69819

You can use the system table Master..spt_values to get a sequential list of integers, and join this to your source adding the integer to the start date each time (and stopping at the end date). e.g.

DECLARE @T TABLE (TimestampStart DATETIME2, TimestampStop DATETIME2);
INSERT @T VALUES ('20131101', '20131105');

SELECT  Date = DATEADD(DAY, spt.Number, t.TimeStampStart)
FROM    @t t
        INNER JOIN master..spt_values spt
            ON spt.Number <= DATEDIFF(DAY, t.TimestampStart, t.TimestampStop)
            AND spt.Type = 'P';

If you are concerned that Microsoft will remove the "undocumented" table Master..spt_values or your dates will be more than 2047 days apart you can generate your list of numbers an alternative way:

DECLARE @T TABLE (TimestampStart DATETIME2, TimestampStop DATETIME2);
INSERT @T VALUES ('20131101', '20131105');

WITH Numbers AS 
(   SELECT  TOP 100000 Number = ROW_NUMBER() OVER(ORDER BY a.Object_ID) - 1
    FROM    sys.all_objects a
            CROSS JOIN sys.all_objects b
)
SELECT  Date = DATEADD(DAY, n.Number, t.TimestampStart)
FROM    @T t
        INNER JOIN Numbers n
            ON n.Number <= DATEDIFF(DAY, t.TimestampStart, t.TimestampStop);

Alternatively, create your own numbers table which can be used over and over again without the need to generate one on the fly each time you need one.

Upvotes: 0

Related Questions