Relative time in SQL
 

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.

Relative time in SQL
 

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.

--
Regards,
Tom Ogilvy

"Igor Sudnik" wrote in message
...
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.

Relative time in SQL
 

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" wrote in message
...
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.

Relative time in SQL
 

Thanks Tom. I'll try that.
"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"Igor Sudnik" wrote in message
...
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.

Relative time in SQL
 

"Tom Ogilvy" wrote in ...

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.

--

Relative time in SQL
 

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.

--
Regards,
Tom Ogilvy


"Jamie Collins" wrote in message
om...
"Tom Ogilvy" wrote in ...

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.

--

Relative time in SQL
 

"Tom Ogilvy" wrote ...

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.

--

Relative time in SQL
 

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.


--
Regards,
Tom Ogilvy


"Jamie Collins" wrote in message
om...
"Tom Ogilvy" wrote ...

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.

--

Relative time in SQL
 

"Tom Ogilvy" wrote ...

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.

--