How Is a Date Stored?
Each implementation has a default storage format for the date and time. This default storage often varies among different implementations, as do other data types for each implementation. The following sections begin by reviewing the standard format of the DATETIME data type and its elements. Then you see the data types for date and time in some popular implementations of SQL, including Oracle, Sybase, and Microsoft SQL Server.
Standard Data Types for Date and Time
There are three standard SQL data types for date and time (DATETIME) storage:
| Data Type | Usage |
|---|---|
| DATE | Stores date literals |
| TIME | Stores time literals |
| TIMESTAMP | Stores date and time literals |
Format and range of valid values for each data type:
| DATE |
|---|
| Format: YYYY-MM-DD |
| Range: 0001-01-01 to 9999-12-31 |
| TIME |
|---|
| Format: HH:MI:SS.nn… |
| Range: 00:00:00… to 23:59:61.999… |
| TIMESTAMP |
|---|
| Format: YYYY-MM-DD HH:MI:SS. nn… |
| Range: 0001-01-01 00:00:00… to 9999-12-31 23:59:61.999… |
DATETIME Elements
DATETIME elements are those elements pertaining to date and time that are included as part of a DATETIME definition. The following is a list of the constrained DATETIME elements and a valid range of values for each element:
| YEAR | 0001to 9999 |
| MONTH | 01to 12 |
| DAY | 01to 31 |
| HOUR | 00to 23 |
| MINUTE | 00to 59 |
| SECOND | 00.000… to 61.999… |
Seconds can be represented as a decimal, allowing the expression of tenths of a second, hundredths of a second, milliseconds, and so on. Each of these elements, except for the last, is self explanatory; they are elements of time that we deal with on a daily basis. You may question the fact that a minute can contain more than 60 seconds. According to the ANSI standard, this 61.999 seconds is due to the possible insertion or omission of a leap second in a minute, which in itself is a rare occurrence. Refer to your implementation on the allowed values because date and time storage may vary widely.
Implementation Specific Data Types
As with other data types, each implementation provides its own representation and syntax. This section shows how three products (Oracle, Sybase, and SQLBase) have been implemented with date and time.
| Product | Data Type | Use |
|---|---|---|
| Oracle | DATE | Stores both date and time information |
| Sybase | DATETIME | Stores both date and time information |
| SMALLDATETIME | Stores both date and time information, but includes a smaller date range than DATETIME | |
| SQLBase | DATETIME | Stores both date and time information |
| TIMESTAMP | Stores both date and time information | |
| DATE | Stores a date value | |
| TIME | Stores a time value |
Note
Each implementation has its own specific data type(s) for date and time information. However, most implementations comply with the ANSI standard in the fact that all elements of the date and time are included in their associated data types. The way the date is internally stored is implementation-dependent.