formatting a date that is stored as text

  • Thread starter Thread starter Guffy9
  • Start date Start date
G

Guffy9

I have a table with a column that contains dates that are of the dat
type text. They are stored in the format mmddyy, so they appear a
numbers. For example 06/11/2004 would be 061104. Is there an easy wa
to convert these to a date type or at least format them as a date?

I know that I could use:


Code
 
I would recommend adding a new field to your table, and using an Update
query to store the value as a true date, not as text. That gives you the
flexiibility to use it as a date: you can do date arithmetic on it, format
it anyway you want, and so on.

The safest way to convert them to a date is to use the DateSerial function:

DateSerial(mid(
.[column], 5,2), mid(
.[column], 3,2),
mid(
.[column], 1,2))

Note that since you're only using a 2 digit year, you may get some surprises
in terms of how it interprets the year. I believe the default is to treat
numbers between 00 and 29 as being 2000 to 2029, and numbers between 30 and
99 as 1930 to 1999. Depending on what operating system you're using, you can
set this through Regional Settings on the Control Panel.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Guffy9 said:
I have a table with a column that contains dates that are of the data
type text. They are stored in the format mmddyy, so they appear as
numbers. For example 06/11/2004 would be 061104. Is there an easy way
to convert these to a date type or at least format them as a date?

I know that I could use:


Code:
Click to expand...
"/" + mid(
.[column], 5,2) AS DateField FROM Table;
 
Back
Top