Is date and time evaluted in query (1 Viewer)

[jpl458]

I need to get the max date from a table, The date field in the table is Date/Time. So, if I run the following query will both date and time be considered in looking for MAX date. I other words, would I get the Max date and time. Concerned that if query is run, and then another record is added after that, and the query is run again on the same day.

SELECT Max(Mastertbl3.calldate) AS MaxOfcalldate
FROM Mastertbl3;

Thanks

 

[ebs17]

SELECT Max(DateValue(calldate)) AS MaxOfcalldate
FROM Mastertbl3

 

[The_Doc_Man]

Not sure what you are asking. If you WANT the maximum date/time combination, MAX of a date variable will do that. If it happens that the date variable includes a time, then time will figure into the comparison. So if you have two dates on the same day but they are a couple of hours apart, the one with the latest date AND time will be selected.

If that is not what you meant, then ask your question again but more specifically.

 

[jpl458]

Not sure what you are asking. If you WANT the maximum date/time combination, MAX of a date variable will do that. If it happens that the date variable includes a time, then time will figure into the comparison. So if you have two dates on the same day but they are a couple of hours apart, the one with the latest date AND time will be selected.

If that is not what you meant, then ask your question again but more specifically.

That's what I wanted to hear, that both date and time were considered in the comparison.

Thank you, Doc.

 

[Galaxiom]

Max will work provided the dates are after the Access zero date, 31/12/1899. Dates including time prior to this will behave differently because the fractional part of the number used to store datetime includes the time as a positive value despite the integer part being negative.

 

[jpl458]

Would the same be true for DMax, as in

Dim DateMAx As Date
DateMAx = DMax("calldate",  "Mastertbl3")

Would time be evaluated as well as the date in Dmax.

 

[GPGeorge]

My favorite way to get answers to theoretical questions is usually "Try it and see what happens."

So, you could create a table with some sample data including dates with times, and try it to see what happens.

 

[jpl458]

Not sure what you are asking. If you WANT the maximum date/time combination, MAX of a date variable will do that. If it happens that the date variable includes a time, then time will figure into the comparison. So if you have two dates on the same day but they are a couple of hours apart, the one with the latest date AND time will be selected.

If that is not what you meant, then ask your question again but more specifically.

Would the same be true for DMax, as in
Code:Copy to clipboard
Dim DateMAx As Date
DateMAx = DMax("calldate", "Mastertbl3")
Would time be evaluated as well as the date in Dmax.

Reply
Report Edit Delete

 

[jdraw]

How exactly is calldate valued in your application?
Does it get values from Date() or Now() or other?

As has been suggested--Try it and see. Let us know what you find.

 

[jpl458]

How exactly is calldate valued in your application?
Does it get values from Date() or Now() or other?

As has been suggested--Try it and see. Let us know what you find.

calldate is date/time, and is created from the properties of files in a folder. The properties show date/time as mm/dd/yyyy hh;mm:s. Just want to know of DMax considers both date and time, in the example above.

 

[jdraw]

I have a table tblFiles where I store Filenames, Folder, LastAccessed, RunDate..

ID Folder FileName FileSize LastAccessed RunDate
10394 C:\users\jp\documents\ AllCode.txt 3056320 03-Jan-23 5:44:18 PM 05-Jan-23
10395 C:\users\jp\documents\ animated-cat-image-0215.gif 51348 05-Jan-23 1:03:33 PM 05-Jan-23
10396 C:\users\jp\documents\ ArchaeologicalInfoYoutube.PNG 363290 05-Jan-23 1:03:33 PM 05-Jan-23
10397 C:\users\jp\documents\ Arthritis_Andy_receipt.pdf 1101462 03-Jan-23 5:44:18 PM 05-Jan-23
10398 C:\users\jp\documents\ atollaUSB_CHubC10629Nov22.PNG 87201 05-Jan-23 1:03:33 PM 05-Jan-23

NOTE: This was spaced for readability before I clicked Save???

?dmax("LastAccessed","tblFiles")
05-Jan-23 1:10:57 PM

Update: I added a picture of the formatted data.

 

[June7]

My response is "of course it does, how could it not?"

The returned value is not shown in the sample so can only presume it is correct.

 

[MajP]

Not sure if this helps but dates are stores as a decimal value. The integer part is days since the base date 30 Dec1899. The decimal is the fraction of a day since. Here are two values and how access stores them.
1/7/2023 3:14:00 PM 44933.6347222222
1/7/2023 3:13:52 PM 44933.6346296296

Usually what you are seeing is a format of this decimal number. This representation or literal value is not stored anywhere "1/7/2023 3:14:00 PM". All sql and vba functions are operating on that numeric value regardless of any formatting.
So you tell me is:
44933.6347222222 < 44933.6346296296

 

[June7]

Use table build tool on post editor menu if you want to present data in tabular form. Can also copy/paste from Excel or Access.

Image still does not show output value.

 

[The_Doc_Man]

The difference between the SQL MAX aggregate function and the DMAX function is that DMAX builds a SELECT MAX(field) FROM table WHERE criteria and then executes it and returns the only thing it retrieved. So a DMAX uses MAX to get what it needs to answer your query. Which is why we do not suggest using domain aggregates in an SQL statement. Oh, they work - but they work way too hard.

 

[Pat Hartman]

Max will work provided the dates are after the Access zero date, 31/12/1899. Dates including time prior to this will behave differently because the fractional part of the number used to store datetime includes the time as a positive value despite the integer part being negative.

True but I'm not sure I'd worry about that for very long

Just want to know of DMax considers both date and time, in the example above.

They are part of the same field so yes. It's like asking if the cents part of a dollar amount is considered when checking an amount field. If you are having trouble, it is because you are storing the data as a string rather than as a date/time data type.

 

[jpl458]

The difference between the SQL MAX aggregate function and the DMAX function is that DMAX builds a SELECT MAX(field) FROM table WHERE criteria and then executes it and returns the only thing it retrieved. So a DMAX uses MAX to get what it needs to answer your query. Which is why we do not suggest using domain aggregates in an SQL statement. Oh, they work - but they work way too hard.

So, is DMax better to than SQL?

 

[June7]

I wouldn't say 'better'. Each have their use. Depends what you want to accomplish. Using domain aggregate functions within a query slows performance.

 

[The_Doc_Man]

So, is DMax better to than SQL?

DMAX is SQL in hiding. It is just an SQL MAX but that call is invisible behind the Domain Aggregate function call. The SQL MAX and DMAX are the same EXCEPT that if you use a DMAX in an SQL statement, you execute the query but then, for each qualified record, you execute a 2nd layer of SQL. So if your WHERE clause qualifies 5000 records, having a DMAX inside the query executes 5001 queries - the outer query and 5000 inner queries. The difference isn't in the code of DMAX vs SQL MAX - it is in the context during which they are each executed.

 

[jpl458]

I wouldn't say 'better'. Each have their use. Depends what you want to accomplish. Using domain aggregate functions within a query slows performance.

Dmax is easier to use. I am working with a miniscule amount of data, so performance is not really an issue.

Thanks, again.