Solved

How to Extract the Time in ms sql server

Posted on 2012-03-13
16
286 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now