Change Data from 19860701 (text) to 07/01/1986 (date)

[Agent_KGB]

I am working with a table that has DOB dates in a text field and stores data
in this format 19860701... i tried changing the data type of the field (in
table design window) but it tells me that i don't have enough memory to
change it...

is there any other way i can convert this data into date format? I sort of
need to validate it against Date()

 

[Dirk Goldgar]

I am working with a table that has DOB dates in a text field and stores
data
in this format 19860701... i tried changing the data type of the field (in
table design window) but it tells me that i don't have enough memory to
change it...

is there any other way i can convert this data into date format? I sort of
need to validate it against Date()

How about this procedure:

1. Create a new date/time field in the table, named "DOBdt".

2. Run an update query that converts the DOB field to the Date data type and
assigns it to DOBdt. Like this:

UPDATE YourTable
SET DOBdt = CDate(Format(DOB,"####/##/##"));

3. After inspecting the resulting values in DOBdt and verifying that they
are correct, go into table design view and delete the field DOB, then rename
DOBdt to DOB.

If there are indexes or relationships defined on the DOB field, you may have
to drop them before deleting and renaming the fields, then reestablish them.

 

[Frank H]

Another approach without using code...
Create a query with a calculated field:
FixDate: DateSerial(Left([thedate],4),Mid([thedate],5,2),Right([thedate],2))

where [thedate] is your text DOB.
You can then turn this into an update query after you've tested it. Or, just
use the calculated field in the query as your source instead of the table
field.

 

[Jeff Boyce]

A point of clarification ...

The "date" you provided could be interpreted, depending on country of
origin, as either the seventh of January, 1986, or the first of July, 1986.
Which version are you expecting...?

Regards

Jeff Boyce
Microsoft Access MVP

 

[Agent_KGB]

this did the trick... thanks a bunch Frank!

Another approach without using code...
Create a query with a calculated field:
FixDate: DateSerial(Left([thedate],4),Mid([thedate],5,2),Right([thedate],2))

where [thedate] is your text DOB.
You can then turn this into an update query after you've tested it. Or, just
use the calculated field in the query as your source instead of the table
field.
--
Frank H
Rockford, IL

I am working with a table that has DOB dates in a text field and stores data
in this format 19860701... i tried changing the data type of the field (in
table design window) but it tells me that i don't have enough memory to
change it...

is there any other way i can convert this data into date format? I sort of
need to validate it against Date()

 

[Agent_KGB]

it's 1 Jul 1986

i think Frank solved my problem...

 

[Bob Quintal]

A point of clarification ...

The "date" you provided could be interpreted, depending on country
of origin, as either the seventh of January, 1986, or the first of
July, 1986. Which version are you expecting...?

Regards

Jeff Boyce
Microsoft Access MVP

Every international standard and every national convention I've ever
seen that defines a year first format also defines the succeeding
number groups in order of decreasing significance. e.g month, day,
hour, minute, seconds, hundredths...

Where the dates are presented with the year in 3rd position, one
nation stands out as doing it the wrong way. Everybody else does it
ddmmyyyy

 

[Larry Linson]

Where the dates are presented with the year in
3rd position, one nation stands out as doing it
the wrong way. Everybody else does it
ddmmyyyy

And, how's your hobby of jousting windmills in your spare time going, Bob?

 

[Bob Quintal]

And, how's your hobby of jousting windmills in your spare time
going, Bob?

LOL, but I acknowledge the truth. Unfortunately I don't have much time
for hobbies, jousting at different windmills as a job takes up all my
time.

 

[Larry Linson]

When I came to the Wonderful World of Access, it was after supporting a
mainframe "decision support system" (which, in retrospect, was much like a
PC database software application). That software originated in Jolly Old
England, in the Midlands (not one of the official 'software development
centers' of the major computer manufacturer for whom I worked). It used the
international standard you described YYYYMMDD, as its default, and didn't
make it quite as easy as Access to manipulate into local standards.

Our U. S. customers just never complained about the standard date format,
and, AFAIK, rarely ever inquired about manipulating it into U. S. date
order... though doing so was not all that difficult. But, the absence of
local defaults meant that we never had to worry if a date was MMDDYYYY or
DDMMYYYY -- those were always "calculated results".

Oh, it would have made life so much easier if there had been a
publicly-available counterpart to newsgroups in those days. There was,
within our company, a worldwide network similar to the Internet, but for
legal and business reasons, it was not accessible from outside the company.

But, that would be when / where I decided that complaining about anyone's
preferred date format was the equivalent of "tilting with windmills".

Larry Linson

 

[Bob Quintal]

When I came to the Wonderful World of Access, it was after
supporting a mainframe "decision support system" (which, in
retrospect, was much like a PC database software application).

Wow, so did I. And I actually wrote most of one, in FoxPlus,

That software originated in Jolly Old England, in the Midlands
(not one of the official 'software development centers' of the
major computer manufacturer for whom I worked). It used the
international standard you described YYYYMMDD, as its default, and
didn't make it quite as easy as Access to manipulate into local
standards.

Our U. S. customers just never complained about the standard date
format, and, AFAIK, rarely ever inquired about manipulating it
into U. S. date order... though doing so was not all that
difficult. But, the absence of local defaults meant that we never
had to worry if a date was MMDDYYYY or DDMMYYYY -- those were
always "calculated results".

Agreed. In the late '80s or early '90s, the company I work for (30
+years) hired a retired USN officer to manage the division. He
shortly after arrival declared that the American date format was to
be used exclusively through the plant. Our European customers
complained, and we then started using the internationally accepted
year-first format.

Oh, it would have made life so much easier if there had been a
publicly-available counterpart to newsgroups in those days. There
was, within our company, a worldwide network similar to the
Internet, but for legal and business reasons, it was not
accessible from outside the company.

But, that would be when / where I decided that complaining about
anyone's preferred date format was the equivalent of "tilting with
windmills".

Actually, I wasn't so much complaining as stating a fact that date
order, except in 1 nation was pretty well standardized, and that the
first responder's observation was not based in fact. My highly
sarcastic sense of humour led me to put my comment in a way that was
letting out my frustration about certain date formats.

Bob

Larry Linson