Strip out Excel timestamp

[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.

 

[Guest]

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.

 

[Douglas J. Steele]

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)

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.

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.

 

[Jeff Boyce]

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>

 

[Guest]

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.

 

[Douglas J. Steele]

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.

 

[Guest]

Thanks Douglas,

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

Cheers,
Ian.

 

[Brian Wilson]

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.

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