Across database structures, dates are ubiquitous: sales date; order date; shipment date; receipt date; created date; last updated date. You might think that with such an abundant presence, dates would be well understood. Sadly, dates hide in the shadows—and many are confused over how dates work. People run a query, get a result, and seem to expect that what one sees, is what one gets. If they see a two-digit year, they believe that is exactly how the data is stored. Similarly, if they see a four-digit year they believe what is kept internally matches that. Often folks have requested database changes to alter how a date is stored. One has such a control over stored formatting of a date only if a date is NOT stored as a date. If a date is stored as a string, obviously the processing can define how it is formatted. If the desire is to change the formatting, under those conditions one may reprocess all the raw data and reformat that given date as one chooses. However, the question should be, “Why?” If one has a date, relational databases support a date datatype. If one uses the date datatype, one has much more flexibility in dealing with that date.
When dealing with date datatypes, one may perform date math. The DBMS has functions allowing one to easily query how many days, weeks, months, years exist between two dates. Also, there are functions allowing for more natural looking queries asking for results of rows having dates within a time unit of a given date, say plus or minus one year. The DBMS also has an almost infinite variety of display formats one may use on a date datatype. If one wishes to see month as a number, or a name, or an abbreviated name, one must only ask. The day-of-week can be part of the displayed date just by request. Along with everything else, yes, one can simply request a two-digit, or a four-digit, year to be displayed. No need to reprocess, just provide your desired formatting within the query pulling data from the database. The order of elements is up to the query writer—month, day, year, or year, month, day.
Is this magic? How does a DBMS support such variability? The secret behind it all is that the DBMS doesn’t exactly store a date within a date datatype column in the first place. What is stored is simply a number, like 1, 2, 3…. Internally, the DBMS has a value of 58,322; but when it retrieves that number for a query among everything going on, the DBMS converts that number into “Wednesday, January 11, 2023,” or whatever, for display. In this manner the amount of space used to store the data is minimized, as it is just a number. What one sees in the query results is created by the logic buried within the DBMS, which is why one may request almost any desired display format. The number 1 is associated with a very specific date and all other dates are derived by adding or subtracting days from that point-in-time. While all DBMSs perform this date miracle, there is little agreement on the starting date. Each DBMS has one, but each vendor’s may be a different value. It could be 1-1-1900, or anything else. One DBMS gave a tip-of-the-hat to the adoption of the Gregorian calendar by the British Empire and used 1753. If dealing with retrieving data from a relational DBMS for columns stored as a date, remember you always have control of the date format you receive. There is no need for worry about reloading or updating database content because of any concerns over a two- or four-digit year.