Working with Dates and Times - Database Design Manual: Using MySQL for Windows - page 130

Working with Dates

Dates can be very problematic in computer systems. For instance, depending on your loca-
tion, the date may be formatted in different ways, the date may be stored in different ways -
which prompted all the worry about the year 2000 bug, and dates may be incorrectly under-
stood, for example:

01-02-01

could be understood as:

•

1 February 2001

•

1 February 1 (year 1)

•

2 January 2001

•

2 January 1,etc.

So when using dates you need to make a decision about how you want to store them and
then stay with it, to save errors being built into your data by the use of wrongly understood
data. One colleague of mine always stores dates as a number of days from year 0, using
something like TO_DAYS() to calculate this. You can then use other functions to format the
output of the date to whatever you want and avoid any problems.
Another issue is the input of dates on webpages. Again a user can enter a date in many
different ways. One way I have seen to avoid this is to have a series of dropdown boxes for
the day, month and year, as shown in Figure 11.18. This prevents the user from entering the
wrong data but will not stop them entering dates such as 31 February, which does not exist.
JavaScript functions are available to pick dates from calendars and personally I have
found these to be most successful for avoiding input errors. An example of one is shown in
Figure 11.19. Instead of clicking directly on a date field, this is marked read only and you
have to click on an icon that invokes the calendar popup. Once you select the date the popup
vanishes and the date is stored as you want it to be in the read-only field.

Figure 11.18 Entering dates through dropdowns.