Relative time in SQL

[Igor Sudnik]

Hi, Guys. Could any one tell me how to write relative time expression in
query. I need something like: WHERE Start time is between "Today
07:00:00 -24H and Today 07:00:00". Any help is greatly appreciated.

 

[Tom Ogilvy]

the basic approach would be

" . . . where StartTime >= '" & Format(Data-1,"mm/dd/yyyy") & " 07:00:00'
and StartTime <= '" & Format(date,"mm/dd/yyyy") & " 07:00:00'"

You would have to insure the format is consistent with what you database
accepts.

 

[RB Smissaert]

It will depend on the database type and
the time format, but it will be something like this:

WHERE
Start_time > 07:00:00 AND
Start_time < 08:00:00

Or depending on the database you could use:
WHERE
Start_time BETWEEN 07:00:00 AND 08:00:00


RBS

 

[Igor Sudnik]

Thanks Tom. I'll try that.

the basic approach would be

" . . . where StartTime >= '" & Format(Data-1,"mm/dd/yyyy") & " 07:00:00'
and StartTime <= '" & Format(date,"mm/dd/yyyy") & " 07:00:00'"

You would have to insure the format is consistent with what you database
accepts.

 

[Jamie Collins]

the basic approach would be

" . . . where StartTime >= '" & Format(Data-1,"mm/dd/yyyy") & " 07:00:00'
and StartTime <= '" & Format(date,"mm/dd/yyyy") & " 07:00:00'"

You would have to insure the format is consistent with what you database
accepts.

It more usual to use the date/time on the database server rather than
use a value from the client e.g. for Jet:

SELECT MyDateTimeCol
FROM MyTable
WHERE MyDateTimeCol > NOW()-1
AND MyDateTimeCol <= NOW()
;

Jamie.

--

 

[Tom Ogilvy]

Sorry, I wasn't aware that the Now function on a database server always
resolved to 7 AM. Thanks for the heads up although that seems kind of
buggy.

 

[Jamie Collins]

Sorry, I wasn't aware that the Now function on a database server always
resolved to 7 AM. Thanks for the heads up although that seems kind of
buggy.

I don't think it does. Did you test this at 7am, perchance? <g>

On my Jet database, I just executed

SELECT Now();

and I got the expected result of

24/08/2004 09:25:35

being the date/time from the server machine.

Jamie.

--

 

[Tom Ogilvy]

It was tongue in Cheek Jamie.
the user wanted to query a 24 hour time period starting at 7AM (or a
specific time - not NOW). So your answer didn't appear to be appropriate
since Now would make the query specific to the current time.

 

[Jamie Collins]

It was tongue in Cheek Jamie.

I had my suspicions.

the user wanted to query a 24 hour time period starting at 7AM (or a
specific time - not NOW). So your answer didn't appear to be appropriate
since Now would make the query specific to the current time.

OP? There was an OP? I was commenting on the questionable approach of
using the date/time from the client machine. It seemed to superfluous
to rewrite what you had posted e.g. as (for Jet):

SELECT start_time
FROM MyTable
WHERE start_time
BETWEEN DATE()-(17/24)
AND DATE()+(7/24)
;

and if there really was an OP then the heads up in my post would be
that their whole approach is wrong in that they should be invoking a
stored procedure that internally not only uses the time on the server
but additionally refers to their calendar table (their business rules
define a business day as being 7am to 7am so they must surely have a
calendar table defining this plus the non-business days such as
weekends and public holidays, right?) Tongue firmly embedded, of
course.

Jamie.

--