Strip out Excel timestamp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have imported some data from an XL file into my Access db. One field is a
date field that also contains a timestamp along with the date. I would like
to strip out the timestamp just leaving the date as dd/mm/yyyy. Is there an
easy way to do this is Access or do I need to make the changes in the XL data
before importing into Access?

Thanks
Ian.
 
Do an update on the field like --
Int([YourFieldName])

Time is stored as a decimal fraction of the date. The integer of datetime
data is the date only.
 
While Int will work for most dates, you can't use it if the dates are prior
to 30 Dec, 1899, as they'll be negative numbers.

DateValue([YourFieldName]) is safer.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


KARL DEWEY said:
Do an update on the field like --
Int([YourFieldName])

Time is stored as a decimal fraction of the date. The integer of datetime
data is the date only.

Ian said:
I have imported some data from an XL file into my Access db. One field is
a
date field that also contains a timestamp along with the date. I would
like
to strip out the timestamp just leaving the date as dd/mm/yyyy. Is there
an
easy way to do this is Access or do I need to make the changes in the XL
data
before importing into Access?

Thanks
Ian.
Click to expand...
Click to expand...
 
Why?

Are you trying to only see the date portion? If so, you can format the
display, and keep the time data -- you don't have to toss it.

Regards

Jeff Boyce
<Office/Access MVP>
 
Thanks for all the replies, its really helped. To answer the "Why" question,
I'm running queries comparing Date in already constructed tables with the
DateTime field in the new table and the time component is giving me spurrious
results in the query result. Second the Time component isn't actually
required, although if you know of a way to cleave the Time and write it to a
new field I could "Have my cake and eat it".

Thanks again
Ian.
 
Don't do it!

Don't separate Date and Time into two separate fields: you've got a
timestamp, and it should be a single field.

If you don't want the time, don't add it. If you want the time, use
DateValue (or TimeValue) to extract only that component of the timestamp in
which you're currently interested.
 
Thanks Douglas,

Noted and understood, it's only the date I need and I'll use your suggestion.

Cheers,
Ian.
 
Douglas J. Steele said:
While Int will work for most dates, you can't use it if the dates are
prior to 30 Dec, 1899, as they'll be negative numbers.

DateValue([YourFieldName]) is safer.
Click to expand...


I see... so this wouldn't work for the pork pie sell-by dates from our local
pub.
 
Back
Top