Convert date format from "yyyy,mm,dd" to "mm,dd,yyyy"

[Guest]

I currently recieve a data file and dates are formated as yyyy,mm,dd. When I
try to convert to mm,dd,yyyy format to perform calculations, I recieve an
error that all data will be lost. Is there a simpler way to manage this
information.

 

[Guest]

Import the date into Access as a text field. Then add another field to the
table, of date/time data type and fill this with an update query, e.g.

UPDATE MyTable
SET MYDate =
DateSerial(Left(MyDateAsText,4),Mid(MyDateAsText,6,2),Right(MyDateAsText,2));

The values in the MyDate column will be true date/time data (which is in
fact a 64 bit floating point number under the skin) and can be operated on in
any way appropriate to that data type, and formatted for output in whatever
date/time format you wish.

The above assumes that the original values are formatted exactly as you've
stated, with no spaces, and the month and day values always being two digits
(i.e. with leading zeros where necessary). The separator character need not
necessarily be a comma, however, but there must be single separator
characters in the positions you've shown the commas.

 

[John Vinson]

I currently recieve a data file and dates are formated as yyyy,mm,dd. When I
try to convert to mm,dd,yyyy format to perform calculations, I recieve an
error that all data will be lost. Is there a simpler way to manage this
information.

What is the datatype of this field - Date/Time or Text?

Note that a field's Datatype is a completely different thing than its
Format. The Format controls how a stored value is changed for display;
the actual stored value does not change. The datatype, on the other
hand, is the actual way the bits and bytes are stored in the database.

I'm GUESSING that the field in your import is a Text field. What you
can do is use a Query to recast it into a (separate) Date/Time field,
using an expression such as

DateSerial(Left([textdate], 4), Mid([textdate], 6, 2),
Right([textdate], 2))


John W. Vinson[MVP]

 

[Guest]

Thanks for the help. Will give this a try.

Import the date into Access as a text field. Then add another field to the
table, of date/time data type and fill this with an update query, e.g.

UPDATE MyTable
SET MYDate =
DateSerial(Left(MyDateAsText,4),Mid(MyDateAsText,6,2),Right(MyDateAsText,2));

The values in the MyDate column will be true date/time data (which is in
fact a 64 bit floating point number under the skin) and can be operated on in
any way appropriate to that data type, and formatted for output in whatever
date/time format you wish.

The above assumes that the original values are formatted exactly as you've
stated, with no spaces, and the month and day values always being two digits
(i.e. with leading zeros where necessary). The separator character need not
necessarily be a comma, however, but there must be single separator
characters in the positions you've shown the commas.