Comparing the Previous time with the current time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Comparing the Previous time with the current time

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-12-21 : 09:49:07

Manish writes "Hi there, any help will be greatly appreciated as i am struggling with this problem :)

I have a field in a database called time which also has the value time in it.

What i want to do is to have a sql query which will display all the records in that table where 5 mins have gone after that time."

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-12-21 : 11:11:44

what is the datatype of your [time] column? It it's not datetime or smalldatetime, post some sample values from that column.

assuming you have a given time that you want to compare...say in a datetime variable called @starttime and your column is of datatype datetime then maybe:


--for records within 5 minutes of @startTime
select <columnList>
from   <myTable>
where  datediff(minute, [time], @starttime) <= 5

if this is not what you're looking for, follow the instructions on this link for clarifying your request:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Be One with the Optimizer
TG

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-21 : 11:55:10

May be the following will help:
[Done lot of assumptions]

Select process_time, CONVERT ( varchar (15) , getdate(),108 ) as [Current Time],
datediff (minute, process_time, CONVERT ( varchar (15) , getdate(),108 )) as [time diff in minutes]
from scan
where process_date = '12/21/2005' and
datediff (minute, process_time, CONVERT ( varchar (15) , getdate(),108 )) > 5

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-22 : 01:03:33

I dont think convert is needed when using DateDiff

Madhivanan

Failing to plan is Planning to fail

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-22 : 10:33:14

Madhi, I agree

This I put in a rush (as always - I failed to plan

).

I didn't write from scratch, instead I got from one of my documents and modified. Anyway thanks for showing that.

Other than making the statement complicated I don't think it does any harm to the output.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources