Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Time calculations for Scheduled Time vs. Actual Time Worked | Excel Discussion (Misc queries) | |||
~ ~ ~ Its All Relative??? | Excel Discussion (Misc queries) | |||
Calculate Ending time using Start Time and Elapsed Time | Excel Worksheet Functions | |||
What relative row I'm in? | Excel Worksheet Functions | |||
How to chnge 35 relative cells to 35 absolute cells at one time. | Excel Worksheet Functions |