Get time from DATE/time field?

  • Thread starter Thread starter Anon
  • Start date Start date
A

Anon

Hi Everyone,
Stupid question here, but does anyone know how to get just
the date out of a date time field? All of the examples
lean toward DD, or MM, but I can't locate anything on all.
Field is currently:

12/01/04 12:24:02
DD/MM/YY HH:MM:SS

I just want to find the ones with date like "04/03/04" for
example...

Thx in advance,

Nine Doors
 
Your subject says "Get time", while the body of the post says "just the
date", so I'll give you both.

To get the Date from a Date/Time field, use the DateValue function (or the
Int function). To get the time, use the TimeValue function.
 
Sorry, actually need to pull both out in different queries
(poorly worded question). Was hoping you might give me an
example, as from the looks of it the Int function only
looks like it will allow you to limit the storage the
fields contain. I'm hoping to pull out the correct values
out from a field with only entering the date (not the time)
When I do it now, because I don't ask for the time, it
returns nothing. Make sense?

Any time for a quicky example perhaps?

Thx again
 
This may not be the fix that you are needing - I got
around this problem in a Business Call Center by storing
both the Date/Time AND the Date (only) separately (both
are entered as default value settings in table).

This allows me to query by date and view exact date/time
of transactions.

Thanks, SR Bandy
 
Sorry, I thought my answer mentioned to use the DateValue and/or TimeValue
functions...

SELECT DateValue(MyDateTimeFIeld) FROM MyTable

will give you only the date part of MyDateTimeField. Similarly,

SELECT TimeValue(MyDateTimeFIeld) FROM MyTable

will give you only the time part.

You can use this in the WHERE clause as well:

SELECT Field1, Field2, MyDateTimeField
FROM MyTable
WHERE DateValue(MyDateTimeField) = #04/03/2004#

For the query above, though, it's probably more efficient to use

SELECT Field1, Field2, MyDateTimeField
FROM MyTable
WHERE MyDateTimeField BETWEEN #04/03/2004# AND #04/04/2004#

Looking closer at your original post, be aware that the date above is the
3rd of April, 2004, not the 4th of March. Regardless of what your regional
settings have as the short date format, Access will not recognize dd/mm/yyyy
format in queries (unless the day is greater than or equal to 13)

For more information about using international dates in Access, check out
Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have at
http://members.rogers.com/douglas.j.steele/SmartAccess.html
 
Technically, that's a violation of database normalization principles, since
one field is totally derivable from another. What happens if someone updates
one field but not the other: which one's correct?
 
Good point - but the fields cannot be edited by users.
I'll look at condensing the fields though.

SR Bandy
 
Thanks for your prompt reply Doug, but this doesn't seem
to be working for me. I have the following:

SELECT roomscleaned.RoomNum, roomscleaned.RoomCleanedBy,
roomscleaned.Comment, roomscleaned.RoomDate
FROM roomscleaned
WHERE (((roomscleaned.RoomDate)=DateValue([roomdate])));

However it doesn't seem to work for me. It only returns
the date when there is no time. For example I have 6
records in my table. 3 are just the date, and 3 are the
date and time. When I execute the query, it returns only
those values with just the date. Conversly, when I remove
the records with the just the date, and leave the 3 with
date/time, it doesn't return anything.

Please Help! What on earth could I be doing wrong?!
Thanks,

Confused in Toronto




-----Original Message-----
 
Thanks Gentlemen,
For all of your help. Definitely all very good points to
consider. Regardless, you both have one more fan.

Many thanks again,

Me
-----Original Message-----
Thanks for your prompt reply Doug, but this doesn't seem
to be working for me. I have the following:

SELECT roomscleaned.RoomNum, roomscleaned.RoomCleanedBy,
roomscleaned.Comment, roomscleaned.RoomDate
FROM roomscleaned
WHERE (((roomscleaned.RoomDate)=DateValue([roomdate])));

However it doesn't seem to work for me. It only returns
the date when there is no time. For example I have 6
records in my table. 3 are just the date, and 3 are the
date and time. When I execute the query, it returns only
those values with just the date. Conversly, when I remove
the records with the just the date, and leave the 3 with
date/time, it doesn't return anything.

Please Help! What on earth could I be doing wrong?!
Thanks,

Confused in Toronto




-----Original Message-----
Sorry, I thought my answer mentioned to use the
Click to expand...
DateValue
and/or TimeValue
functions...

SELECT DateValue(MyDateTimeFIeld) FROM MyTable

will give you only the date part of MyDateTimeField. Similarly,

SELECT TimeValue(MyDateTimeFIeld) FROM MyTable

will give you only the time part.

You can use this in the WHERE clause as well:

SELECT Field1, Field2, MyDateTimeField
FROM MyTable
WHERE DateValue(MyDateTimeField) = #04/03/2004#

For the query above, though, it's probably more
Click to expand...
efficient
to use
SELECT Field1, Field2, MyDateTimeField
FROM MyTable
WHERE MyDateTimeField BETWEEN #04/03/2004# AND #04/04/2004#

Looking closer at your original post, be aware that the date above is the
3rd of April, 2004, not the 4th of March. Regardless of what your regional
settings have as the short date format, Access will not recognize dd/mm/yyyy
format in queries (unless the day is greater than or equal to 13)

For more information about using international dates in Access, check out
Allen Browne's "International Dates in Access" at
http://members.iinet.net.au/~allenbrowne/ser-36.html or what I have at
http://members.rogers.com/douglas.j.steele/SmartAccess.ht
Click to expand...
m
l


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)






.
Click to expand...
.
Click to expand...
 
Back
Top