By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,401 Members | 928 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,401 IT Pros & Developers. It's quick & easy.

Timestamps

P: n/a
Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

Thanks,
Scott

Aug 31 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Date/time fields are actually serial numbers formatted to display either
date, time or both. If you're storing the values with "general" format,
just change the format (in your form/query/report) to one of the available
time formats and use that to sort on. You will probably want to sort on
date+time to accommodate values over multiple 24hr periods.
-Ed
<sc********@gmail.comwrote in message
news:11**********************@50g2000hsm.googlegro ups.com...
Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

Thanks,
Scott

Aug 31 '07 #2

P: n/a
sc********@gmail.com wrote:
Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.

Thanks,
Scott
Here's an example. Using format, the value is a string. Cdate converts
it back to a date field.
? dt
8/31/2007 7:06:12 AM
dt = Now()
? format(dt,"mm/dd/yyyy")
08/31/2007
? format(dt,"hh:nn:ss")
07:06:55
? cdate(format(dt,"mm/dd/yyyy"))
8/31/2007
? cdate(format(dt,"hh:nn:ss"))
7:06:55 AM
Aug 31 '07 #3

P: n/a
sc********@gmail.com wrote:
Does anyone know how to pull the date and the time from a timestamp
field in msaccess? Here is an example.

8/30/2007 11:53:00 AM

Ultimately, I would like to be able to place the date in one field and
the time in another. i would then like to be able to sort the time.
Any help would be appreciated.
You've received some other suggestions; here is another.

You said "timestamp field" so to me that indicates the field is of type
Date.

To pull just the date portion and then just the time portion you could
use the DateValue() and TimeValue() functions. I opened the Immediate
window (Ctrl-G) and typed the text on the lines with question marks and
received the results displayed on the following line:

? datevalue(now())
9/1/2007
? timevalue(now())
11:45:55 PM

If your data field was named "StartTimeStamp" then you might do this in
a SQL query:

SELECT
DateValue([StartTimeStamp]) As StartDay
, TimeValue([StartTimeStamp]) As StartTime
FROM yourTable
.....

This query would not be updateable since StartDay and StartTime are now
*calculated* values, fwiw.

--
'--------------------------
' John Mishefske
' UtterAccess Editor
' 2007 Microsoft Access MVP
'--------------------------
Sep 2 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.