|
how to get the maximum and minimum of two dates
sample
the value of time1(variable) is 8:00 AM
the value of time2(variable) is 5:48 PM
i want to get the minimum and maximum of two time
corect me if i am wrong
"Select Timein,Timeout from WorkSchedule where Timein >="&time1&" and Timeout <="& time2
am i correct?
thanks,
Pls. help
this is impt.
|
|
|
|
|
|
|
Your post is titled "maximum and minimum", but your query has nothing to do with MAX and MIN. Your query:
"Select Timein,Timeout from WorkSchedule where Timein >="&time1&" and Timeout <="& time2
Will give you all records that apply -- not the single MAX or MIN. If this is what you want, then you are done.
If what you are doing is not what you want, then I need to know more specifically what you want. Here is how you could return the columns for the record with the earliest Timein that also falls within your Time1 to Time2 range. (This would be Minimum Timein that applies.)
sql = "select *" & _
" from WorkSchedule" & _
" where Timein = (" & _
" select MIN(Timein)" & _
" from WorkSchedule" & _
" where Timein >= `" & time1 "`" & _
" and Timeout <= `" & time2 & "`" )
" and Timeout <= `" & time2 & "`"
It aint pretty, but that`s a way to do it. To find the MAX record, you`d adjust this same query accordingly. So you`d run 2 queries. One to get the MIN record and one to get the MAX record.
|
|
|
|
|
|
|
|
|
|