Of the different capabilities offered by SQL engines, dates are one of the most nonstandard areas. You'll need to be sure to check your database engine documentation for each of these functions and make sure that it is supported. Also make sure you know about any different
parameters that may be required by your specific engine.
In the near future, products will become more and more standardized and will support these functions directly and identically. For now, just
be sure you check the documentation.
Dates can be formatted differently in different engines. For example, with some engines, the date can be shown as 05/02/99, May 2, 1999, and
02 May 1999, with all being recognized as valid representations of the date. In this case, the engine will recognize the information as a date
and will save the value correctly. In other engines, it may be necessary to present the date exactly as you have dates defined on your system,
which takes into account localization for different countries' representation of dates. It may also be necessary to add a pound sign (#),
before and after the date.
Again, be sure to check the documentation for your specific engine to determine if there are any of these types of requirements.
Sometimes it is quite useful to be able to extract individual parts of a date, such as day of the month,
month, or year. To do this, SQL provides the DAY(), MONTH(), and YEAR() functions. These are supported
by all of the database systems except Oracle.
These functions perform in the same manner, and the syntax for each is as follows:
The following code displays the DateOfBirth of each film club member, as well as the day of the month,
month, and year of their birth:
SELECT DateOfBirth, DAY(DateOfBirth), MONTH(DateOfBirth), YEAR(DateOfBirth)
ORDER BY YEAR(DateOfBirth);
Executing the preceding statement provides the following results:
To see the actual version of you SQL Engine (because depending on the version some functions/statements will not work), in MySQL use: