Date format, mix, set all to 4 digit year

[Emblem1]

I have a table with 1300+ records. There is a field with date information which has a mix of 2 digit and 4 digit entries for the year portion. I set the properties of the field to use 4 digit, but that only affect editing and adding. I am looking for an easier way to have access change all dates to be correct 4 digit. No records are older than '93, then newest ones current. So it needs to recodgnize that all dates 93-99 are 1993-1999, and 00-07 are 2000-2007. Some dates in 2007 are entered as "xx/xx/2007", but most everything else is xx/xx/xx.

Is there a way to have access to change/update the records to the 4 digit format.

Thanks.

 

[SkipVought]

Hi,

A DATE is a DATE. If your field is a REAL DATE, the format makes no difference, since the underlying VALUE of Date/Time is just a NUMBER.

However, it seems that your field is not a REAL DATE. Check the Field Data Type. From what you state, it seems that your field is a TEXT data type, in which case you can parse the values in a query into Year, Month & Day and use the DateSerial function to return a Date Value. Use an IIF statement to determine the LEN of the value and for the sort years, concatenate either "19" or "20" based on the yy value.

Skip,

_[red][/red]

 

[johnherman]

Following up on Skip's post:

UPDATE DATE_FIELD SET DATE_FIELD = SUBSTR(DATE_FIELD,1,6) || '19' || SUBSTR(DATE_FIELD,7,2)
WHERE SUBSTR(DATE_FIELD,7,2) > '90' AND LENGTH(DATE_FIELD) = 8

And run a similar update for the 2000's. Also, I am not sure if the Syntax I used is completely correct for Access SQL.

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[lespaul]

John,
Double pipe is the concatenation operator for other forms of SQL and I don't think they work with JetSQL....additionally, Access doesn't use the SUBSTR, you have to use LEFT, MID and RIGHT functions.

But Skip's point was that you are only looking at a FORMATTED date. A DATE is 39171.52545 where the integer is the date and the decimal is the time and it doesn't MATTER what the format is....as long as it is a date field type.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[Emblem1]

Well, thanks to all. But I am really confused.

If I read this right, even though Leslie states that John may be wrong in his approach, it look like he was trying to insert 19 ifo the 90's dates, and assumed to place a 20 ifo the rest, if they were formatted in 2 digit.

I was hoping there was a macro/query already written for this type of thing I could download and run on my db.

 

[PHV]

Bottom line, for storing date values simply use a DateTime field, period.

 

[Emblem1]

The table I have was imported from a lotus db earlier this year. So the dates were brought with the records. I changed the format to DATE/TIME, but it gives errors to fields that do not have the 4 digit year format.

 

[SkipVought]

What is the DATA TYPE of your Date Field?

If the Data Type is indeed DATE, then this is a tempest in a teapot. A DATE is a DATE! Exit discussion.

If the Data Type is NOT a DATE, then you have a parse and convert into a NEW FIELD that IS, indeed a Data Type DATE.

Skip,

_[red][/red]

 

[Emblem1]

When I did the import, it set the field type as text. Changing this to a date type causes the error. I have also set the input mask to Short Date. Is this causing the error?

 

[lespaul]

So in fact you do NOT have a date field in your table....you have a text field that stores a representation of your date.

As Skip says, what you need to do is create a NEW field in the table that is a DATE. Then you will need to take your string date and convert it to a REAL date and insert that information into the NEW field.

 

[SkipVought]

TEXT is not DATE, and never the twain shall meet!

"...and convert into a NEW FIELD that IS, indeed a Data ..."

Skip,

_[red][/red]