[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

How to modify time intervals in SELECT query?

Hi

I have the query below supplied by another clever EE member.  It returns amounts for 10 second intervals.  My question is, if I want to change the interval to eg. 45 mins is it as simple as changing every "ss" to "mi" and every 10 to 45?

====================================
select convert(varchar(19),dateadd( ss, interval*10, CONVERT(int,getdate()) ), 121) as interval, amount
from (
 select DATEDIFF(ss,convert(int,getdate()),[timestamp])/10 as interval, SUM(amount) as amount
 from MyTable
 where [timestamp] >= convert(int,getdate())  -- this will limit the data to today's date, from midnight
 group by DATEDIFF(ss,convert(int,getdate()),[timestamp])/10
)T
====================================

The original question is here if you want a description of the table involved: http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_27245666.html

Thanks in advance!
0
Danzigger
Asked:
Danzigger
2 Solutions
 
pivarCommented:
Hi,

No, mm stands for Month, use mi for minutes instead. Look here for the different values http://msdn.microsoft.com/en-us/library/ms186819.aspx

/peter
0
 
DanziggerAuthor Commented:
Thanks - yeah it says "mi" in the post.

Cheers.
0
 
pivarCommented:
I'm sorry, I read mi, and thought mm. Maybe I need more coffe?

Was that a help? Or do you have other questions regarding this problem?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
rosenkrantzCommented:
Hi

yes, but be aware that you are using the same interval for the sub query (10 sec.) maybe you need to correct it also to get the result you want.

/Thomas
0
 
VipulKadiaCommented:
Yes Danzigger,
You just have to change 'ss' to 'mi' and 10 to 45. That's it.
0
 
DanziggerAuthor Commented:
Ha, no worries pivar - we all need more coffee!  Thanks for looking at it.

It sounds like I've got my answer thanks guys.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now