Convert Lont Integer into Date and Time

[danka]

Hello:

I am importing file that has date and time fields in the long integer format
as follows:

Date_occurred Time_occurred
20051109 04562
20051109 132640

Can I do the conversion in the import specification? I tried to change data
type to date/time and I get Type conversion failure in ImportErrors Table.

Can I convert in a query by using a conversion function? What function
should I use?

Would appreciate your help.

Danka

 

['69 Camaro]

Hi, Danka.

Can I do the conversion in the import specification?
No.

I tried to change data
type to date/time and I get Type conversion failure in ImportErrors Table.

That's why you can't convert with the import specification. These numbers
don't correspond to any valid date or time format.

Can I convert in a query by using a conversion function?

You'll need several VBA functions to accomplish this if that's what your
data looks like.

What function
should I use?

First, import the data into a table, but import these Long data types as
Text data types. Next, create two more fields in this table: one named
DateOccurred and the other named TimeOccurred, both as Date/Time data types.
Create an update query that assigns dates to the DateOccurred field and times
to the TimeOccurred field, based on the corresponding text fields. Try:

UPDATE tblMyTable
SET DateOccurred = CDate(MID(Date_occurred, 1, 4) & "/" & MID(Date_occurred,
5, 2) & "/" & MID(Date_occurred, 7, 2)),
TimeOccurred = CDate(Format(IIF((LEN(Time_occurred) < 6), Time_occurred &
"0", Time_occurred), "00:00:00"))

.. . . where tblMyTable is the name of the table. And be advised that a
Date/Time data type can hold both the Date_occurred and Time_occurred values
in one field, so you don't need two fields.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[danka]

Hello:

Thank you very much for answering my questions.

Your solution works and returns the answer in the following format for the
date:
20051109 11/9/2005
20051109 11/9/2005

I also tried the following expression:
Left([date_occurred],4) & "/" & Mid([date_occurred],5,2) & "/" & Mid
([date_occurred],7,2)

Thank you very much for your response.

 

['69 Camaro]

You're welcome. Glad it helped.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

Hello:

Thank you very much for answering my questions.

Your solution works and returns the answer in the following format for the
date:
20051109 11/9/2005
20051109 11/9/2005

I also tried the following expression:
Left([date_occurred],4) & "/" & Mid([date_occurred],5,2) & "/" & Mid
([date_occurred],7,2)

Thank you very much for your response.

Hello:

I am importing file that has date and time fields in the long integer
format
as follows:

Date_occurred Time_occurred
20051109 04562
20051109 132640

Can I do the conversion in the import specification? I tried to change
data
type to date/time and I get Type conversion failure in ImportErrors
Table.

Can I convert in a query by using a conversion function? What function
should I use?

Would appreciate your help.

Danka