Solved

How to Extract the Time in ms sql server

Posted on 2012-03-13
16
292 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 2
  • +2
16 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
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
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

624 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