List all minutes between two DATETIME values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

List all minutes between two DATETIME values

Author

Topic

haz33
Starting Member

15 Posts

Posted - 2008-05-22 : 11:37:50

Hi, does anyone know a SQL statement that will take two DATETIME values and list all minutes in between them like so:

START VALUE: 5/22/2008 10:00
END VALUE: 5/22/2008 10:10

RESULT:
5/22/2008 10:00
5/22/2008 10:01
5/22/2008 10:02
5/22/2008 10:03
5/22/2008 10:04
5/22/2008 10:05
5/22/2008 10:06
5/22/2008 10:07
5/22/2008 10:08
5/22/2008 10:09
5/22/2008 10:10

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 11:42:57

[code]
SET DATEFORMAT mdy

DECLARE @StartDate,@EndDate

SELECT @StartDate='5/22/2008 10:00',@EndDate='5/22/2008 10:10'

SELECT DATEADD(mi,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEDIFF(mi,DATEADD(mi,number,@StartDate),@EndDate)=0[/code]

haz33
Starting Member

15 Posts

Posted - 2008-05-22 : 11:53:35

If I do that, I only get one row as a result:

2008-05-22 10:10:00.000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 12:11:21

quote:
Originally posted by haz33

If I do that, I only get one row as a result:

2008-05-22 10:10:00.000

SET DATEFORMAT mdy

DECLARE @StartDate,@EndDate

SELECT @StartDate='5/22/2008 10:00',@EndDate='5/22/2008 10:10'

SELECT DATEADD(mi,number,@StartDate)
FROM master..spt_values
WHERE type='p'
AND DATEADD(mi,number,@StartDate)<=@EndDate

haz33
Starting Member

15 Posts

Posted - 2008-05-22 : 12:37:29

Looks like it works. Thanks for the help!

haz33
Starting Member

15 Posts

Posted - 2008-05-22 : 14:22:53

One follow-up on this......I see that the master..spt_values table only contains a limited number of records (2,047, I believe). So if I needed a huge list of times, I couldn't get more than that. What do I do if I need more?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-22 : 14:26:41

quote:
Originally posted by haz33

One follow-up on this......I see that the master..spt_values table only contains a limited number of records (2,047, I believe). So if I needed a huge list of times, I couldn't get more than that. What do I do if I need more?

If you want more than that, create a count table yourselves containing number of values you want and use it in place of spt_vaues.spt_values is simply used as a count table in above query.

haz33
Starting Member

15 Posts

Posted - 2008-05-22 : 15:13:21

This was one way to generate a list of dates between two separate dates. It's completely independent of other tables and can create a seemingly endless number of rows.

with mycte as
(
select cast('2008-05-01' as datetime) DateValue
union all
select DateValue+1 as DateValue
from mycte
where DateValue+1<'2008-05-10'
)
select DateValue
from mycte
OPTION (MAXRECURSION 0)

I'd like to use something similar to this to generate a list of all times between the two dates in five-minute increments, but I can't figure out how to do it. Any ideas would be much appreciated.

haz33
Starting Member

15 Posts

Posted - 2008-05-22 : 15:42:09

Never mind. I think I got it. Here's the solution for five-minute intervals between two dates.....

with mycte as
(
select cast('2008-05-01' as datetime) DateValue
union all
select dateadd(n,5,cast(DateValue as datetime))
from mycte
where dateadd(n,5,cast(DateValue as datetime))<'2008-05-10'
)
select DateValue
from mycte
OPTION (MAXRECURSION 0)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources