Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Records for last 2 hours Expand / Collapse
Author
Message
Posted Tuesday, March 25, 2014 4:14 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:52 AM
Points: 253, Visits: 761
Out Table has date as '2014-04-25' and time as 12:00 AM , 1:01 Am ,12:10 pm etc....

I need to see records for last two hours ...

How should I do so?
Post #1554714
Posted Tuesday, March 25, 2014 4:31 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, November 6, 2014 1:00 PM
Points: 5,333, Visits: 25,277
With the little data you have posted ... use Books on Line and check the use of the BETWEEN statement


From Books on Line:


test_expression BETWEEN begin_expression AND end_expression


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1554717
Posted Tuesday, March 25, 2014 4:32 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:23 PM
Points: 4,049, Visits: 9,214
You will need to use DATEADD as well.
http://technet.microsoft.com/en-us/library/ms186819.aspx



Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1554718
Posted Tuesday, March 25, 2014 5:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:11 PM
Points: 20,864, Visits: 32,902
sharonsql2013 (3/25/2014)
Out Table has date as '2014-04-25' and time as 12:00 AM , 1:01 Am ,12:10 pm etc....

I need to see records for last two hours ...

How should I do so?



select
*
from
YourTable
where
datecolumn between dateadd(hour,-2,getdate()) and getdate();





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1554724
Posted Wednesday, March 26, 2014 5:59 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:09 AM
Points: 363, Visits: 1,091
sharonsql2013 (3/25/2014)
Out Table has date as '2014-04-25' and time as 12:00 AM , 1:01 Am ,12:10 pm etc....

I need to see records for last two hours ...

How should I do so?

Does this mean you have two columns?
What is the data type of the column(s)?
Post #1554901
Posted Wednesday, March 26, 2014 9:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:52 AM
Points: 253, Visits: 761
Let me be a little bit more clear on that...
There are two columns

CurrentDate Date,
Time Varchar(10)


If I do , Select * from Data
Order By Date Desc , Time Desc shows the following results
The result is :
2014-03-26 3:01 AM
2014-03-26 2:01 PM
2014-03-26 2:01 AM
2014-03-26 12:01 PM
2014-03-26 12:01 AM
2014-03-26 11:01 AM *********** Shouldn't this be on top of :01 am?

I need to see the latest Date and Time in an order... If I resolve this I will be able to resolve my issue.
Post #1555056
Posted Wednesday, March 26, 2014 10:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 19, 2014 10:09 AM
Points: 363, Visits: 1,091
You can combine the two thus
SELECT CAST(CurrentDate AS DATETIME) + CAST(Time AS DATETIME) FROM Data

This will be a DATETIME value. So you might use a CTE to combine the values then do the compare
Post #1555060
Posted Wednesday, March 26, 2014 10:54 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 18, 2014 8:52 AM
Points: 253, Visits: 761
Thanks . That helps.
Post #1555090
Posted Wednesday, March 26, 2014 11:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 8:11 PM
Points: 20,864, Visits: 32,902
If you would like some real help solving your problem please read the following article. It will show you what you need to post and how to post it to get the best possible responses to your question. Remember, we can't see what you see so you need to help us help you.

http://www.sqlservercentral.com/articles/Best+Practices/61537/



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1555113
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse