MS Query and Time Part of Timestamp

[SPMRYT]

I am trying to query an external database where I only want to return the rows that fall between the hours of 11 AM and 3 PM. I can't for the life of me figure out how to set up the criteria in MS Query to bring back the rows that meet the 11 AM to 3 PM window. The column being queried is a timestamp with date and time present. Any help will be greatly appreciated. Thanks.

 

[Fazza]

hi, What about something like "WHERE Hour(your_field) > 11 AND Hour(your_field) < 15" ? Fazza

 

[SPMRYT]

The external database is Oracle and it did not like HOUR. I have been trying to use the "TO_DATE" operator but there does not seem to be a way to ignore the date portion of the column in my criteria. I tried using a wild card for the date,
e.g. to_date('%%-%%-%%%% 11:00','MM-DD-YYYY HH:MI')
but that only generated an error.

 

[Fazza]

Within MS Excel & Access, HOUR(CDate(field_name)) should be ok. I don't know anything specific about querying Oracle, sorry. I don't know TO_DATE either. I wonder is this better addressed in an Oracle forum?

 

[pd_it_guy]

may I suggest, the cleanest way is something like
...
WHERE DATEPART("h",your_timestamp_variable)>=11:00 AND
DATEPART("h",your_timestamp_variable)>=15:00

That is how you are supposed to parse date and time combined.

There is also a rather peculiar identifier for query that has the identifier 'td' before the string to id it as a timestamp, but that is for elapsed dates, not what we have here.

This is from memory- plenty of references and examples on the web. Should work with oracle, informix, etc.

 

[pd_it_guy]

I thought I had better check and get it right. The proper SQL syntax for a timestamp in MSQuery is as follows; we happen to have 6 digit 24 hour time; you can alter as need be. the variable event_start and event_end was the date only, but we had to account for time in the tables.... also note... curly braces:

Hope this helps

AND (cc.occ_date>={ts '" & event_start & " 00:00:00'}) AND (cc.occ_date<={ts '" & event_end & " 23:59:59'})