How to compare two times that are stored in DateTime datatype??

[Guest_imported]

I have a few queries in Access97 that need to be converted to stored procedures on an SQL Server 7.

It looks like (this is shortened):

....
WHERE (((resourcestable.AvailableFromDate)<Date()) AND ((resourcestable.AvailableToDate)>Date()) AND ((resourcestable.AvailableFromTime)<Time()) AND ((resourcestable.AvailableToTime)>Time()))
....

In access97 it's possible to compare the time that is stored in the AvaialleFromTime-column with TIME() (which is the time at the moment right now). SQL Server doesn't have any similar TIME()-function as I've seen.

I wonder how I could compare the times (without dates) to see if the time that is stored in the table is smaller or bigger than the time right now. I.e. if 12:00:00 is bigger or greater than 08:00:00.

Any help is greatly appreciated!

sincerely Bob Nachbar

 

[FLUZZI]

Welcome Bob Nachbar!

Take a look in then Books on line and search for CONVERT functon.I Can remenber right now if a time function exists, but I know about GETDATE() function thats return the date and time. Use the CONVERT function with GETDATE() to extract the time and compare to other time. Search for date functions in the books on line... I think there is a bether way to do this.

in the case you keeping with this problem...may be I have some sample at my office.

I Hope this help! Tchau!

 

[Guest_imported]

Thanks Fluzzi!

I have been searching the Books Online (and I still search in there and on the www) but I can not find a solution to my problem.

I have now made a procedure that can compare two times, like this:

DECLARE @T1 DATETIME
DECLARE @T2 DATETIME
DECLARE @Time1 varchar(100)
DECLARE @Time2 varchar(100)
set @T1 = GETDATE()
set @T2 = (SELECT availablefromtime FROM resourcestable WHERE Resourceid=2)
SET @Time1 = LTRIM(STR(DATEPART(hh,@T1)))*60*60 + LTRIM(STR(DATEPART(mi,@T1))*60 + LTRIM(STR(DATEPART(s,@T1))))
SET @Time2 = LTRIM(STR(DATEPART(hh,@T2)))*60*60 + LTRIM(STR(DATEPART(mi,@T2))*60 + LTRIM(STR(DATEPART(s,@T2))))
IF ( CAST(@Time1 AS int) < CAST(@Time2 AS int) )
PRINT &quot;Time1 < Time2&quot;
ELSE
PRINT &quot;Time1 >= Time2&quot;;


So this works to compare a time from the database with the time at the moment.
But I need to modify this procedure and be able to call this procedure (with modifications) from my other Stored procedure. It looked like this in the Access97 query:

...
AND ((resourcestable.AvailableFromTime)<Time())
...

So instead of ((resourcestable.AvailableFromTime)<Time()) I need to call the other storedprocedure that compares 2 times (only time, the date should be separated from the time).

I hope someone can help me solve this problem!

regards, Bob

Thanks for the welcome!