Querying dates & times

[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

 

[Duane Hookom]

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?

 

[Digi777]

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.

 

[John Spencer]

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

 

[Digi777]

Duane,

Any way to do this with access commands?

 

[John W. Vinson]

Any way to do this with access commands?

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)

 

[Duane Hookom]

I asked two questions and you replied with no answers and a vague question.