Change date from 1/1/09 to 1/1/2009 2

[hefly]

I have inherited a database with dates entered with the following format in the date fields: 1/1/09 (m/d/yy and mm/dd/yy)

I need to change the dates to the following format:
mm/dd/yyyy

Fortunately, all of the years entered with two digit are in this century, i.e. 2008, 2008, 2010 etc. I am hoping this will simplify the update.

Is there a simple way to update my records to a four digit year? I would prefer a two digit day also, but I don't think this will be problematic in using the date functions I need to run with these records.

Hefly

 

[PHV]

Seems like your dates are stored as strings instead of dates !

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[randy700]

As PHV stated, it looks like you have text fields instead of date fields. If you want to keep them as text fields, you can change the format by running and update query.
Something like...

UPDATE myTable SET myField = Left(myField,len(myField)-2) & "20" & right(myField,2)

Randy

 

[traingamer]

I would prefer a two digit day also, but I don't think this will be problematic in using the date functions I need to run with these records.

The date stored as text shouldn't be a problem with date functions either, as long as the text is a valid date. Access is very forgiving of formats.
These work identically:

DateDiff("d",[dtField],"11/1/09")
DateDiff("d",[dtField],#11/1/2009#)
DateDiff("d",[dtField],[textField])

where dtfield is stored as a date and textField as text.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[hefly]

Thanks all of you for the help... I really want a date-field. I tested the data to see if changing the Text-Date Field to a Date-Field would delete the data and I am pleased to report, that it did not. I was able to change the data from Text data type to DateField data type.

PLUS, if a text date was entered as 12/1/09, changing the data type to Date field data type, the record was changed to 12/01/2009.

Thanks Again...

Hefly