How to Extract the Time in ms sql server

Hi

 i am having   a column  called timestamp: i have data for every mintue:
i need to extract the five mintue data:
in oracle  i use :
select (concat(concat(to_char(timestamp,'HH24:MI'),' - '), (select to_char(timestamp +(ROWNUM* 5/1440), 'HH24:MI') from dual))) as timestamp .... so on the query

what command should i use in   ms sql server:
to get the outout below: please help:

2008-03-14 10:49:00
2008-03-14 10:48:00
2008-03-14 10:47:00
2008-03-14 10:46:00
2008-03-14 10:45:00
2008-03-14 10:44:00


out put should be like these:
11:27 - 11:32 (5 minute)
12:52 - 12:57
13:01 - 13:06
13:50 - 13:55
10:32 - 10:37
10:52 - 10:57

Thanks
nrajasekhar7Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ephraim WangoyaCommented:
try

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

Open in new window

0
Ephraim WangoyaCommented:
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'

Open in new window

0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

keyuCommented:
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)
0
nrajasekhar7Author Commented:
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:
0
winston33Commented:
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.
0
nrajasekhar7Author Commented:
sorry , the above query is not  working  , i am not getting the desired out put.like above.
0
winston33Commented:
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
0
nrajasekhar7Author Commented:
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...
0
winston33Commented:
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
0
nrajasekhar7Author Commented:
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.
0
winston33Commented:
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
0
nrajasekhar7Author Commented:
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.
0
jogosCommented:
<<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
0
winston33Commented:
Here is the FROM clause. Please follow jogos advice and learn the fundamentals behind the query. All the links included in this post will help.

FROM A, (SELECT dateadd(HOUR, number, @StartDate) As StartDate,
                                    dateadd(HOUR, (number+1), @StartDate) As EndDate, ROW_NUMBER() over(ORDER BY dateadd(HOUR, number, @StartDate)) As RowNum
                  FROM master..spt_values n with (nolock)
                  WHERE number between 0 and DATEDIFF(HOUR, @StartDate, @EndDate)
                        AND type = 'p'
                  ) rows
where name='Alli' and
class='COR' and
TimeStamp BETWEEN StartDate AND EndDate
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nrajasekhar7Author Commented:
Thanks a lott for your support
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.