Changing Date format

D

Don

I have data I import from and an excel file that is in the following format:

20060831

My problem is that I want it in this format: 08/31/2006 in my database.

How do I do this?
 
D

Douglas J. Steele

If you can convert it into a Date field, it doesn't matter what format you
want, because Dates are not stored in any particular format in Access:
they're stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day.

To convert that number to a date, try

CDate(Format(MyDateValue, "####\-##\-##"))

Replace MyDateValue with the actual name.
 
D

Don

I think the first line of Douglas' post may indicate the problem I am having
"If you can convert it into a Date Field". I seems that the Excel file is
number - long interger. When trying to convert this format to a date format I
either get an overflow error when appending excel file to a formatted table
or a value of "error" in the append query for the date field. I used the
following expression to change the format without sucess:

CDate(Format([Request Date], "####\-##\-##"))

Oddly Access changes the expression to Request Date1: CDate(Format([Request
Date],"#-#-#")) and of course the value received is "error" in the return for
the field. How can I over come this problem without changing the excel file
before I extract the data into Access?
--
Thanks,

Dennis


Douglas J. Steele said:
If you can convert it into a Date field, it doesn't matter what format you
want, because Dates are not stored in any particular format in Access:
they're stored as 8 byte floating point numbers, where the integer portion
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal portion represents the time as a fraction of a day.

To convert that number to a date, try

CDate(Format(MyDateValue, "####\-##\-##"))

Replace MyDateValue with the actual name.
Click to expand...
 
D

Douglas J. Steele

Hmm. Interesting!

CDate(Format([Request Date], "0000\-00\-00"))

seems to work.


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Don said:
I think the first line of Douglas' post may indicate the problem I am
having
"If you can convert it into a Date Field". I seems that the Excel file is
number - long interger. When trying to convert this format to a date
format I
either get an overflow error when appending excel file to a formatted
table
or a value of "error" in the append query for the date field. I used the
following expression to change the format without sucess:

CDate(Format([Request Date], "####\-##\-##"))

Oddly Access changes the expression to Request Date1:
CDate(Format([Request
Date],"#-#-#")) and of course the value received is "error" in the return
for
the field. How can I over come this problem without changing the excel
file
before I extract the data into Access?
Click to expand...
 
Top