How do I Reverse the Format of a Date

[Peter Dam]

I have access 2003

For uploading the Date of Birth from my Access DataBase to my gmail account, I need the notation yyyy-mm-dd in my table.

However, for calculating the age in Access, I need to rotate this notation to dd-mm-yyyy

There is no need to store this roated DoB in my table, but I can make an extra field if it can be stored in the table.(But I do not know if this is possible).

So I need a query that does the notation rotate for me, wherafter I can calculate the exact age with Age:(Date()-([DoB]/ 365,25

Who can help me to rotate the DoB notation.

Thanks in Advance

Peter

[Narender Sagar]

Hi Peter,
You don't have to change anything in table. Just create a query and select your DoB field. Right click on the this selected field and click 'Properties'. In properties window, go to 'General' tab and go to 'Format'. Just type yyyy-mm-dd over there in the format section.
Remember: You have to check whether you want "-" or "." or "/" in between yyyy mm dd, and type accordingly.
And thats all.!
Run the query, You'll see the desired result.

[Peter Dam]

Hi Narender.

I tried your suggestion, but I can't get it work.


I created a query with Colomn "Name" and Colomn "Birthdate"

Both fields are in my table "All Date".

My field "Birthday" in my table All Data is yyy-mm-dd (defined in Data Type - General. Also my input mask is set-up as: 0000-00-00;0;_

When I Right click in the Query Field "Birthday" and thereafter Properties-General-Format, I see already dd-mm-yyy

The result of running the query is that the data is still in the format yyyy-mm-dd. It seems not to display the data in the format as indicated in Format.


Is there anything I do wrong?
Do you need any additional info from me?

Kind regards
Peter

And : Thanks in Advance for helping me.

[NeoPa]

Peter, understand this :

Your date data is stored as a Date. Not as a formatted date. If you can get past this concept (unfortunately so many people haven't) then the whole process becomes much easier to work with.

It is important therefore, to make sure your date data is actually stored in a DateTime type field for starters (Some people fail to do this and none of the rest of it works as you cannot format a string value for instance).

Once this is ensured you simply need to understand the difference between formatting a result using the format property, and returning a formatted string as a value. Using the Format property to format the results of a query field is fine for display purposes (and only for display purposes). In such a situation the underlying data remains the same (actually a double-precision floating value - but you probably won't have to worry about those details in most of what you do). If you need to work on the formatted data though then you will need to use the Format() function instead.

The format strings for your two formats are :

1. "dd-mm-yyyy"
2. "yyyy-mm-dd"

Lastly, DateDiff() can be used to determine the number of days between two date/times. This can prove important for correctly reflecting ages.

[Narender Sagar]

Peter,
Although Neo has explained very well about dates concept. There is nothing more to add, however, I just want you to check your system date in control panel.
Just follow path : Control Panel- Region and Language or (Clock, language and Region in Windows 7)- Change Date time or number format- Additional settings- Date (tab) and then under Date Format check Short Date format. By default it should be mm/dd/yyyy format. Set it as dd/mm/yyyy (if you want it this way). and then you can check it in your database.
Remember, a computer understand dates as in numbers and it starts with 1. So for a computer 1 means 1/1/1900. So when you use DateDiff() function, it always gives results in number of days (which you can convert to years by dividing with 365.2425)
HTH

[NeoPa]

I would urge caution when considering changing any of the System date formats. I would go as far as to say don't do it unless you know it to be wrong for your region.

Formats within an Access database should be managed exclusively within that Access database. It is not responsible development to make changes to a system when the changes pertain simply to a single database.

Certainly ensure the System date formats are correctly set for your system and region, but do not set them up to reflect the requirements of a single database as you will simply be causing yourself a bunch of problems.

PS. DateDiff() certainly can determine the difference in days if requested, but can also do it for other periods as well. The parameter value for using days is "d" if that helps.

[Peter Dam]

Hi Narender Sagar and NeoPa

Thanks for helping me.

My Birthday record was set-up in the Text format. This because I needed yyyy-mm-dd for Export to Gmail. I could only get this format in my query if I did set it up like this.

At that time I did not know that I could select in a query yyyy-mm-dd or dd-mm-yyyy.

I now have changed in the table the properties of my Birthday record to Date/Time and it works perfectly. In my query I can, under format, indicate yyyy-mm-dd for export to GMAIL and in an other Column dd-mm-yyyy for my birthday calender and calculation of the current age.


Thank you both for yr help.

Kind regards,
Peter

[Narender Sagar]

Good to know that.. :)
regards,