15 Minute Groupings of data

Question

I am trying to select data points in 15 minute intervals.  I get the data I need but am unable to display a usable label (I am graphing the data on an ASP.net page).  What I want to do is each hour group the data each quarter hour 00, 15, 30 and 45 and display it as 7:00, 7:15, 7:30 and 7:45.  My select uses a UNION of 4 select statements (one for each quarter hour).  My original thought was to concatenate the appropriate increment to the hour (datepart(hour,datefield)+ ':15') but I can not get the formatting correct to make it work.  Any ideas or suggestions??

 

Thanks

 

Answer 1

Is it a Data Mining topic?

Could you try a similar formula in your query, please?

SELECT OriginalDateTime,
DATEADD(MILLISECOND, - DATEPART(MILLISECOND, OriginalDateTime), 
  DATEADD(MINUTE, -(DATEPART(MINUTE, OriginalDateTime) % 15), 
    DATEADD(SECOND, - DATEPART(SECOND, OriginalDateTime), OriginalDateTime)
  )
) AS TimeRoundedToQuarterHour
FROM YourTable

Kind regards,
Zoli

---

-- Zoltán Horváth
-- MCITP SQL Server Business Intelligence Developer 2005, 2008
-- Please mark posts as answered or helpful where appropriate.

Answer 2

Or something like this:

SELECT OriginalDateTime,
DATEADD(MINUTE, -(DATEPART(MINUTE, OriginalDateTime) % 15), 
CAST(CONVERT(varchar(16), OriginalDateTime, 121) AS datetime)) AS TimeRoundedToQuarterHour
FROM YourTable

Hope this helps as well.

Regards,
Zoli