CODEWITHSUNDEEP
sqlsql-server-2008sequence
Pawich S.
Pawich S.

Reputation: 455

How to generate row of time sequences using SQL (SQL-Server)

Right now, I want to create function that generate row of time sequence from date x to date y using SQL.

The function have two date inputs: x and y. They are date without time (i.e. time is 00:00:00). The function will look like this:

function (@x date, @y date)

Then, the result will generate rows of time sequence from date x to date y. Each time has difference of 15 minutes. The result will not include date y.

For example, if I call function ('2013-06-19', '2013-06-21'), the result will be:

'2013-06-19 00:00:00'
'2013-06-19 00:15:00'
'2013-06-19 00:30:00'
...
'2013-06-19 23:30:00'
'2013-06-19 23:45:00'
'2013-06-20 00:00:00'
'2013-06-20 00:15:00'
...
'2013-06-20 23:30:00'
'2013-06-20 23:45:00'

Upvotes: 1

Views: 1870

Answers (1)

Serge
Serge

Reputation: 6692

From a performance point of view, you'd rather create a table with all the dates you could need.

Even if you'd rather not have such a table then you'll need a tally table (tables of numbers) anyway.

tally table

select  
    DATEADD(mi, n * 15, @x) 
from (
    select ROW_NUMBER() over (order by (select null)) - 1
    from
    master.dbo.spt_values
) as virtualTally(n)
where n < datediff(HH, @x , @y) * 4

output is:

2013-06-19 00:00:00.000
2013-06-19 00:15:00.000
2013-06-19 00:30:00.000
...
2013-06-20 23:15:00.000
2013-06-20 23:30:00.000
2013-06-20 23:45:00.000

Upvotes: 1

Related Questions