Microsoft Access Date Format

  waydekirrane 11:02 25 May 2005

i've had some good help on this forum and i'm wondering if you can help again! when i type or output any date in a microsoft access query it gives it in a US date format ie 1/25/2005 instead of 25/01/2005. i've tried changing date formats everywhere i know but it's not working. i recently upgraded to xp pro sp2 and this is when the prob started.

  skeletal 12:47 25 May 2005

The US date format is a pain in the backside. You are correct waydekirrane, no matter how many times or where you change the format, it’ll go wrong somewhere else!!

To state the obvious: I take it you have set all your date formatting in the Access tables and queries to what you want (in the query design table, highlight the field, right click/properties and you can set the date format to be different from the format you chose in the underlying table).

However, to the most peculiar bit of all this, are you by any chance generating your queries in code? i.e. generating a SQL statement where you may be adding in a date, in SQL? The odd thing is that SQL always uses the US date format, no matter what you do. Thus, if you are doing this, you must acknowledge all your code will generate US format. You have to think carefully about what you are doing, and at the end of the process, convert back to UK format in the final display of date.

However, you also mention SP2 and my SQL thoughts would not be affected by SP2 at may be a red herring.


  GRFT 13:03 25 May 2005

The US date format, Year-Month-Day, is perfectly logical, particularly in a computer environment. Think about it. Everyone should adopt it.

  skeletal 15:26 25 May 2005

GRFT, not everyone uses computers and there are many, many instances of computer outputs being printed for these users.

The same goes for the difficulty in setting language from US to UK; posts on this topic are common.

Or (IMO), the most logical system of units is SI yet there are those who still use deg F instead of deg C (funnily enough in the USA, I think).

Surely, the point is that, if we accept different people have different requirements, let's make it easy to change from one system to another.


  Access Moron 23:40 25 May 2005

you can always force the date


will work in sql

  waydekirrane 09:55 26 May 2005

the thing is before i upgraded to windows xp pro i didn't have this problem. i sometimes write my queries in sql using the format 'YYYY-MM-DD' which is something i'm used to, but when i switch back to the design view in access it changes to 'MM-DD-YYYY' which is very confusing and very very annoying. why is it that before xp pro i didn't have this problem at all! another microsoft bug!?!

  Nosmas 11:03 26 May 2005

Whilst I agree that Year-Month-Day is logical - particularly for sorting into date order - that is NOT the US date format. The US format uses Month-Day-Year, and this becomes confusing to those used to Day-Month-Year when looking at dates where both the day and month are less than 13.

  GRFT 11:34 26 May 2005

Agreed. That's why we should all learn to use the same format.

  skeletal 13:15 26 May 2005

waydekirrane, what you are saying is exactly what I mean. The effect I was trying to explain is clearly seen when you switch between design view and SQL view. I did not realise it was in any way related to the operatng system (I use XP), I thought it was soley due to the SQL problem I talked about.

It confused me for a while as well! I worked around it by accepting the fact that the SQL will always be in US format.

Access Moron's solution will also work, but I just didn't adopt this solution on the occaision I needed a work around.


  waydekirrane 13:41 26 May 2005

bit confused:

xp home - i got dd-mm-yyyy in design view regardless of what i put into sql.

xp pro - i get mm-dd-yyyy in design view. i changed nothing else, only the os.

must be something to do with os or settings in access?

  skeletal 14:43 26 May 2005

Sorry waydekirrane, I don't know what's going on! I use XP pro and see what you describe, and as I say I just thought it was the SQL issue. I've never tried switching views using another operating system although my databases are used on different OSs without a problem. I wonder what would have happened if I had developed my systems using XP home and hadn't used my SQL workaround?! Dates would sometimes be wrong depending on which OS was being used??!! Very helpful!

To show you you aren't on your own with odd Access bugs; one reported to me this morning is that my perfectly formatted Access Report gets totally screwed up if the user "sends to Word". Now I know there are issues when using the export to Word facility which is why I don't use it in its standard form, but the really odd thing is that if I view the screwed output on my machine, it is screwed up in a different way to the user's screw up!

IMHO I think that there are many bugs in Access 2003 and I can't wait for service pack 2!

I think I'm going back to a quill pen and parchment...probably be quicker in the long run.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Samsung Galaxy A8 review: Hands-on

Illustrator Juan Esteban Rodriguez on creating highly detailed official film posters for Star Wars…

iMac Pro review

Meilleurs drones (2018)