Solved

How to Extract the Time in ms sql server

Posted on 2012-03-13
16
288 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:nrajasekhar7
  • 6
  • 5
  • 2
  • +2
16 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 37718600
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
 
LVL 32

Expert Comment

by:ewangoya
ID: 37718612
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
 
LVL 9

Expert Comment

by:keyu
ID: 37718660
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Expert Comment

by:keyu
ID: 37718664
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
 

Author Comment

by:nrajasekhar7
ID: 37718772
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
 
LVL 2

Expert Comment

by:winston33
ID: 37720243
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
 

Author Comment

by:nrajasekhar7
ID: 37723136
sorry , the above query is not  working  , i am not getting the desired out put.like above.
0
 
LVL 2

Expert Comment

by:winston33
ID: 37723240
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
 

Author Comment

by:nrajasekhar7
ID: 37723514
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
 
LVL 2

Expert Comment

by:winston33
ID: 37724947
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
 

Author Comment

by:nrajasekhar7
ID: 37727658
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
 
LVL 2

Expert Comment

by:winston33
ID: 37727763
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
 

Author Comment

by:nrajasekhar7
ID: 37728087
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
 
LVL 25

Expert Comment

by:jogos
ID: 37728258
<<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
 
LVL 2

Accepted Solution

by:
winston33 earned 500 total points
ID: 37729336
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
 

Author Closing Comment

by:nrajasekhar7
ID: 37736023
Thanks a lott for your support
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question