Forum Discussion
Formatting 2-digit year in MS Access. How?
- Hello Gustav,
And that was the way to do it. The option you referred to, I didn't realize that it was existing. Thank you very much.
Regards from the Netherlands.
Keeping in mind that what you are talking about is a FORMAT FOR DISPLAY PURPOSES, and not the actual DATE VALUE STORED, you can do this with a Format() expression wherever you need to DISPLAY that FORMATTED STRING.
Format(Month(Date), "mm") & "/" & Format(Year(Date),"yy")
or
Format(Month(Date), "mm") & "-" & Format(Year(Date),"yy")
Having done this, you are DISPLAYING a string, not a date, so beware of trying to use it for any further date logic.
- Thank you for the above information.
I still wander why it is possible to get the 2-digit month number but not the 2-digit year number.
As I wrote before the 2-digit month number wasn't a problem within Access; it's the 2-digit year that keeps on showing up as a 4-digit number. Why?- I should have been more clear. The year is a 4-digit value. To DISPLAY it as a 2 digit number, you apply the appropriate format to it.
- Hello George,
And that is the problem. I've checked the Access helppage for the format and the helppage suggested it was possible to get a 2-digit year number. Their instructions worked for the 2-digit month number, but not for the 2-digit year number.
I was not aware how Access is storing its date/time. Thank you for this explication.
Please, also read my other reply.
This is such a common request that I feel it's useful to jabber on about it a bit more.
Access ALWAYS stores dates internally as a full date and time. It does so in the form of a double.
The part of the double to the left of the decimal is the number of elapsed days since the starting date for Access dates, which is December 30, 1899 (or 12/30/1899 in the US format used by Access be default)
The part of the double to the right of the decimal is the elapsed time since midnight of the date specified. For example, 8:00AM is 8 hours after midnight, or 8/24th of a day, or 1/3 of a day, or .3333
So the current date and time might be 44334.3333 representing 8:00 o'clock in the morning on the 18th of May, 2021.
Note that in my example, I actually used several different FORMATS to express dates and times. We, humans, have learned to interpret a number of DATE FORMATS so we just do it without thinking.The important point is that no matter how the date and time are expressed, the underlying value is what Access actually stores in the table. And it stores that value as I explain above.
So, when working with dates and times in a relational database application, be careful not to confuse the ability to FORMAT a date for DISPLAY with the actual value it represents.