Querying dates & times

  • Thread starter Thread starter Digi777
  • Start date Start date
D

Digi777

I am struggling with running a query where the field is both a date and a
time. i.e. 01/25/2010 3:40 pm. When running the query, it doesn't see
anything. After entering the query criteria, it only shows up as
#01/25/2010# and no data selected... Any suggestions?

Thx, D
 
Can you tell us how you would like this to work? Do you want to ignore the
time portion of the value or is the time significant to the criteria?
 
Hi... the time is very important. I am trying to find the first timestamp of
the day and the last timestamp of the day....

I really appreciate your responding!

David.
 
SELECT DateValue(FieldDateTime) as JustDate
, Min(FieldDateTime) as Earliest
, Max(FieldDateTime) as Latest
FROM SomeTable
GROUP BY DateValue(FieldDateTime)

If you want to restrict that to a specific date or date range add a WHERE
clause. For instance to get all the dates in January of 2009 use something
like the following.

SELECT DateValue(FieldDateTime) as JustDate
, Min(FieldDateTime) as Earliest
, Max(FieldDateTime) as Latest
FROM SomeTable
WHERE FieldDateTime >= #2009-01-01# and FieldDateTime<#2009-02-01#
GROUP BY DateValue(FieldDateTime)

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Any way to do this with access commands?
Click to expand...

ummm...

other than the Access commands which John Spencer suggested?

You'll need a Query. A Query consists of SQL. (The query grid is nothing but a
tool to make it easier to build SQL). That SQL can contain calls to builtin
Access functions such as DateValue.

In case you didn't see it here's John's reply, repeated:


SELECT DateValue(FieldDateTime) as JustDate
, Min(FieldDateTime) as Earliest
, Max(FieldDateTime) as Latest
FROM SomeTable
GROUP BY DateValue(FieldDateTime)

If you want to restrict that to a specific date or date range add a WHERE
clause. For instance to get all the dates in January of 2009 use something
like the following.

SELECT DateValue(FieldDateTime) as JustDate
, Min(FieldDateTime) as Earliest
, Max(FieldDateTime) as Latest
FROM SomeTable
WHERE FieldDateTime >= #2009-01-01# and FieldDateTime<#2009-02-01#
GROUP BY DateValue(FieldDateTime)
 
Back
Top