How to convert date formats from dd-mmm-yr to yr-mmm-dd?

[YACHTIE]

I have over 30 tables with mixed date formats any easy way to standardize all to the "yr-mmm-dd" format? I have several tables with over 10000 records and several date columns.

I'd hate to have to do this manually, is there a routine that anyone knows of that looks at the date value and switches them?

TIA

 

[Remou]

How can you have mixed date formats? Is this a text field?

 

[lespaul]

a database stores a date field as an integer in the number of days since 12/30/1899 and the time in the decimal portion of the number.
Today is: 39171; 9:21 in the morning is .3901709375

the function Now() on 3/30/2007 at 9:21 would return:
39171.3901709375



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

 

[JoeAtWork]

The following code will change the default format for all date fields. Unfortunately, I found it doesn't change the format for any existing forms or reports.

Private Sub Command1_Click()
    Dim tdf As TableDef
    Dim fld As DAO.Field
    
    For Each tdf In CurrentDb.TableDefs
        If Left(tdf.Name, 4) <> "msys" Then
            For Each fld In tdf.Fields
                If fld.Type = dbDate Then
                    fld.Properties("Format") = "medium date"
                End If
            Next
        End If
    Next
    
    MsgBox "Done!"
    
End Sub

 

[JoeAtWork]

For your purposes you would want it to be "yy-mmm-dd" instead of "medium date".

But you will only see the difference if you open the table, the forms and reports retain their previous formatting.

 

[YACHTIE]

Thanks for the responses, they will help me somewhat, however I guess I did not fully explain my complete dilemma.

Currently my data contains a mixture of date formats, most are correct in that they contains:"yy-mmm-dd" some and I would guess approx. 20% are in the "dd-mmm-yr" format.
It is the latter 20% I need to correct.

I need some code to determine if the first two date chars are ">07 or <31" then switch the format around for me.

And I don't know how to do that. Now I know that will only correct some of the bad dates but at least it will correct the obvious ones (the ones that get noticed).

Look forward to any suggestions on how to accomplish this.

Regards,

 

[Remou]

As I asked on 30 Mar 07 15:14, how can you have mixed date formats? Is this a text field?

 

[YACHTIE]

NO....it is NOT a text field, all are date fields all formats are set to "yr-mmm-dd" BUT for reasons I won't go into here over time these databases have been wide open and every T,D&Harry have been &^%$$$ around in there with their PC's regional settings NOT being what they should have been.

I have come to the conclusion that in MsAccess for all intends and purposes the medium date format with input mask "99\->L<LL\-00;0;" : ("yr-mmm-dd") is not viewed any different than "dd-mmm-yr".

That's my dilemma.....

 

[SkipVought]

YACHTIE,

A DATE is a DATE regardless of FORMAT. The FORMAT is what you SEE, usually in a report or form.

A table that has 07-Apr-02 and another that has 02-Apr-07, have identical DATE VALUES if the formats are yy-mmm-dd and dd-mmm-yy respectively. I, personally, would recommend AGAINST any kind of AMBIGUOUS format (both yy-mmm-dd and dd-mmm-yy ARE ambiguous)

Skip,
[sub]
[red][/red]
[/sub]

 

[JoeAtWork]

As Leslie pointed out, date fields are actually stored as a number, so it doesn't matter what format you see the date as in your reports, forms, queries, etc.

If somebody's regional settings were "mm/dd/yyyy" and they entered "01/02/2007", and assuming there were no input masks, the date would be interpreted as January 2, 2007. If the regional settings were "dd/mm/yyyy", the date would be interpreted as February 1, 2007. But either way, the value being stored in the database is not "01/02/2007", it is some number like 38928.

So reading the first two characters doesn't help you, because what you are reading is the date after it's been converted back to a human-readable format. It does not tell you how the person entered the date in the first place. If their regional settings were "mm/dd/yyyy" they would see "01/02/2007", if my settings were "dd/mm/yyyy", I would see "02/01/2007", but in both cases the date is actually January 2, 2007.

 

[YACHTIE]

Thannks Joeatwork,

I was afraid I was screwed and you just confirmed it for me.

Have a great day!!