Can you use time in a query?

[katzi4eva]

Hi,

Ive found alot of reference to using dates to find records but not time, I need a query to pull up records entered on that day before lunch time and another query will pull the records entered after lunch.

receipt date is date/time field
receipt time is date/time field

to get records for that day i use date() which works but cannot get the time part to work??

Please help!!!!

 

[ConnorGiles]

Hi Katzi,

Just taking a stab here - but date/time format always shows date originally

Try changing the format too Short time -

Find the formats for date/time here -
https://support.office.com/en-us/ar...ata-Type-3251a423-3dd7-446e-be65-c7293eddbb43

 

[ConnorGiles]

I think the best option for you may be Long Time actually

Hope this helps

 

[katzi4eva]

Well think have maybe worked out one way of doing it,
for the afternoon query i have used

>Now() And <Now()+1 which only selected the record i had entered in the afternoon

and the morning one i just used <now() but it means i have to remember to run the queries at the right times!

 

[ConnorGiles]

you could also take the easy road and use : Time()

works the same as Date()

but obviously it displays time instead.

 

[katzi4eva]

OK, next question!

my field "receipt date" is date/time, short date which in my table stores 27/11/2014, happy days!

when i export this data, it shows "receipt date" as 27/11/2014 00:00 - can i take out the 00:00 for the export?

 

[namliam]

You can use timeserial function to "create" a time like: Timeserial(12,0,0) for noon.

Assuming your date/time field is one you can simply add date() to the timeserial to get the date/time

 

[ConnorGiles]

I should think that the format "dd/mm/yyyy" would do that would it?

if that's the format, time shouldn't be showing

 

[katzi4eva]

It still does it in the export but not in my table!

 

[ConnorGiles]

SELECT DATE_FORMAT(date_column_here,'%d/%m/%Y') FROM table_name;

Try that one

 

[katzi4eva]

proper daft question, where do i put that? in the query that i use to create the export?

 

[ConnorGiles]

By the way, Just for the information.

you need to create a query based on the table and export the query (using Format([Field],'dd-mm-yy')).

This is because exporting does not format the data. A Date/Time field is formatted as a real number which, when displayed, is treated as a Date/Time value and formatted as such. So the time will be displayed

 

[ConnorGiles]

proper daft question, where do i put that? in the query that i use to create the export?

That would have been placed within your SQL.