How to Extract the Time in ms sql server

try

Select dateadd(SECOND, number*5, '20120314')
from master..spt_values n with (nolock)
where number between 0 and 60 
and type = 'p'

Select allOpen in new window

or add the start and end if you like

select dateadd(SECOND, number*5, '20120314') [Start], dateadd(SECOND, (number+1)*5, '20120314') [End]
from master..spt_values n with (nolock)
where number between 0 and 60 
and type = 'p'

Select allOpen in new window

try this links..

https://www.experts-exchange.com/questions/23284413/selecting-5-minute-intervals-from-a-time-series.html

https://www.experts-exchange.com/questions/23846027/MS-SQL-Query-to-count-records-within-5-minute-intervals.html

select dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0), count(*)
from YourTable
group by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)
order by dateadd(n, (datediff(n, 0,YourDateColumn)/5)*5,0)

here it is counting the records i dont want to count the records : Please find my below sql query :
 Query written in Oracle : How should i convert these query in ms sql server.

SELECT
(concat(concat(to_char(timestamp,'HH24:MI'),' - '), (select to_char(Ctimestamp +(ROWNUM* 5/1440), 'HH24:MI') from dual))) as COLLECTIONTIME,
name,
class,
(value/10) as MEASUREMENT,
FROM A
where name='Alli' and
class='COR' and
to_char(timestamp,'MM/DD/YYYY HH24:MI') between to_char(:param3,'MM/DD/YYYY HH24:MI') and to_char(:param4-(5/1440),'MM/DD/YYYY HH24:MI')
order by  1

 i need the above query  in ms-sqlserver 2008:  please.


ed the out put to be :
09:00- 09:05    
09:05-09:10
09:10-09:15
09:15:09:20
like these   depends on my parameter value:

similary  i want the for onehour :
09:00-10:00
10:00-11:00
11:00-12:00
12:00-13:00
13:00-14:00
 
from my datetime column  if i pass paramerter  :09/10/2008 09:00:00 to  09/10/2008 15:00:00
then i should get like extract for one hour data:
09:00-10:00  
10:00-11:00
11:00-12:00
so on
14:00-15:00
Please  help:

To get the time element of the timestamp parameter, try the following:

SELECT CONVERT(TIME, CONVERT(VARCHAR(2), DATEPART(hour, <timestamp>)) + ':' + CONVERT(VARCHAR(2), DATEPART(minute, <timestamp)))

I put this in a user-defined scalar function and just call the function in your where clause.

Hope this helps.

sorry , the above query is not  working  , i am not getting the desired out put.like above.

What is the output you are getting? When I run it here, I get results like the following:

14:01:00.0000000
14:02:00.0000000
14:08:00.0000000
14:16:00.0000000
14:24:00.0000000
14:29:00.0000000
14:32:00.0000000
14:33:00.0000000

iam also getting like that only , But my requirement is
it should give like :
these format: if i pass for that day 2012-03-15 09:00 to 11:00

collection time:
09:00- 09:05    
09:05-09:10
09:10-09:15
09:15-09:20
09:20-09:25
09:25-09:30 like these untill
10:55-11:00
it should extract

SELECT
(concat(concat(to_char(timestamp,'HH24:MI'),' - '), (select to_char(Ctimestamp +(ROWNUM* 5/1440), 'HH24:MI') from dual))) as COLLECTIONTIME,name,
class,
(value/10) as MEASUREMENT,
FROM A
where name='Alli' and
class='COR' and
to_char(timestamp,'MM/DD/YYYY HH24:MI') between to_char(:param3,'MM/DD/YYYY HH24:MI') and to_char(:param4-(5/1440),'MM/DD/YYYY HH24:MI')
order by  1

if run thes query in orace:
SELECT
(concat(concat(to_char(timestamp,'HH24:MI'),' - '), (select to_char(Ctimestamp +(ROWNUM* 5/1440), 'HH24:MI') from dual))) as COLLECTIONTIME
i got the sameout put as i needed.
collectiontime:
09:05-09:10
09:10-09:15
09:15-09:20
09:20-09:25
09:25-09:30 like these untill
10:55-11:00

how should i convert that sql query into ms -sql server
same output i want in  ms sql server.
Please help its urgent for me...

Try this out. I have substitued variables for what will be parameters to your stored proc. I added the LEFT function because I do not think there is a built-in format in SQL Server to return only HH:MM (could be wrong on this).

DECLARE @Start DATETIME
DECLARE @End DATETIME

SET @Start = CONVERT(DATETIME, '2012-02-01 10:00:00.000')
SET @End = CONVERT(DATETIME, '2012-02-01 14:00:00.000')

select      LEFT(CONVERT(TIME(0), dateadd(MINUTE, (RowNum-1)*5, @Start)), 5) + '-' +
            LEFT(CONVERT(TIME(0), dateadd(MINUTE, (RowNum)*5, @Start)), 5)
FROM      (      SELECT ROW_NUMBER() over(ORDER BY dateadd(MINUTE, number*5, @Start)) As RowNum
                  FROM master..spt_values n with (nolock)
                  WHERE number between 0 and DATEDIFF(MINUTE, @Start, @End)/5
                        AND type = 'p'
                  ) rows

the above one is working if i  hardcode the @STARTDATE  & @ENDDATE,
But i need that @startdate to be the Column name  like 'TimeStamp'
in between cluase  i will pass the paramets :

select (LEFT(CONVERT(TIME(0), dateadd(MINUTE, (RowNum-1)*5,TimeStamp)), 5) + '-' +
            LEFT(CONVERT(TIME(0), dateadd(MINUTE, (RowNum)*5, TimeStamp)), 5)
    (SELECT ROW_NUMBER() over(ORDER BY dateadd(MINUTE, number*5, TimeStamp)) As RowNum
                  FROM master..spt_values n  (nolock)
                  WHERE number between 0 and DATEDIFF(MINUTE,'2012-01-10 11:30:00.000' ,'2012-01-10 13:30:00.000' )/5
                        AND type = 'p'
                ) rows ,
 
name, class,
(value/10) as value,
FROM A
where name='Alli' and
class='COR' and
LEFT (CONVERT(TIME(0), dateadd(MINUTE, (RowNum-1)*5,collectiontime)), 5) + '-' +
            LEFT(CONVERT(TIME(0), dateadd(MINUTE, (RowNum)*5, collectiontime)), 5) between
            '2012-01-10 11:30:00.000' and '2012-01-10 13:30:00.000'
order by  1

Please suggest how should i get from the existing column name.
Thanks for the response,
Please i need urgent.

I don't really understand your query above. The @StartDate and @EndDate are simply parameters passed into your proc to build the range string and use in the WHERE clause to compare the data column against.

Your FROM clause will simply compare the field with the converted @StartDate/@EndDate, like this:

FROM A, (SELECT dateadd(MINUTE, number*5, @StartDate) As StartDate,
                                    dateadd(MINUTE, (number+1)*5, @StartDate) As EndDate, ROW_NUMBER() over(ORDER BY dateadd(MINUTE, number*5, '2011-03-29 09:30:00.000')) As RowNum
                  FROM master..spt_values n with (nolock)
                  WHERE number between 0 and DATEDIFF(MINUTE, @StartDate, @EndDate)/5
                        AND type = 'p'
                  ) rows
where name='Alli' and
class='COR' and
TimeStamp DateCreated BETWEEN StartDate AND EndDate

Thanks a lott finally  i got the Desired out put .
Thanks for your help.

Please similarly can u help he to extract the one hour interval :
11:00-12:00
12:00-13:00

so on as per my parameters.

Thanks in advance.

<<finally  i got the Desired out put .
..;
Please similarly can u help he to extract the one hour interval :>>
You could mention what did the trick )

And learn from the given examples.

From a datetime adding an hour DATEADD(hour ,1,@date) gives you the next hour
http://msdn.microsoft.com/en-us/library/ms186819.aspx

Thanks a lott for your support