Date and Time Datatypes
All DBMSs support datatypes designed for the storage of date and time values (see Table D.3). Like numeric values, most DBMSs support multiple datatypes, each with different ranges and levels of precision.
| Datatype | Description |
|---|---|
| DATE | Date value |
| DATETIME (also known as TIMESTAMP) | Date time values |
| SMALLDATETIME | Date time values with accuracy to the minute (no seconds or milliseconds) |
Caution
Specifying Dates There is no standard way to define a date that will be understood by every DBMS. Most implementations understand formats like 1999-12-30 or Dec 30th, 1999, but even those can be problematic to some DBMSs. Make sure to consult your DBMS documentation for a list of the date formats that it will recognize.
Tip
ODBC Dates Because every DBMS has its own format for specifying dates, ODBC created a format of its own that will work with every database when ODBC is being used. The ODBC format looks like {d `1999-12-30'} for dates, {t `21:46:29'} for times, and {ts `1999-12-30 21:46:29'} for date time values. If you are using SQL via ODBC, be sure your dates and times are formatted in this fashion.