Ispirer Website
Ispirer Capabilities - MySQL Migration
Free Trial
MySQL Data Types - DATETIME
Versions: MySQL 5.x, 4.x and 3.23
| MySQL - DATETIME | |
|---|---|
| Syntax | DATETIME |
| Data | Date data and time (year, month, day, hours, minutes and seconds) |
| Range | January 01, 1000 00:00:00 to December 31, 9999 23:59:59 |
| Special Values | MySQL DATETIME accepts zero date value '0000-00-00 00:00:00' (or 0, '0') and 0 for day and month (can be disabled by SQL Mode NO_ZERO_IN_DATE. Note: MySQL ODBC returns zero date as NULL |
| Error Handling | if SQL Mode ALLOW_INVALID_DATES is specified, MySQL checks only month in the range 0-12 and day in the range 0-31 and invalid day can be inserted (Nov 31 i.e.) |
| Storage Size | 8 bytes |
MySQL DATETIME - Equivalents in Other Databases
| Database | Data Type and Conversion |
|---|---|
| Oracle | TIMESTAMP(p), January 1, 4712 BC 00:00:00.000000000 to December 31, 9999 23:59:59.999999999 (nanoseconds accuracy) |
| SQL Server | DATETIME, January 01, 1753 00:00:00.000 to December 31, 9999 23:59:59.997 (1/300 accuracy) DATETIME2 and DATETIMEOFFSET, January 01, 0001 00:00:00.0000000 to December 31, 9999 23:59:59.9999999 (100 nanoseconds accuracy) SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy) |
| PostgreSQL | TIMESTAMP(p), January 01, 4713 BC 00:00:00:000000 and December 31, 5874897 23:59:59:999999 (microseconds accuracy) |
| Sybase ASE | DATETIME, January 01, 1753 12:00:00.000 AM to December 31, 9999 11:59:59.999 PM (with accuracy 1/300 second) SMALLDATETIME, January 01, 1900 00:00 to June 06, 2079 11:59 (minute accuracy) |
| Informix | DATETIME, January 01, 0001 12:00:00.00000 AM to December 31, 9999 11:59:59.99999 PM (with 10 microseconds accuracy ) |
| Teradata | TIMESTAMP(p), January 01, 0001 00:00:00.000 to December 31, 9999 23:59:61.999999 (microseconds accuracy) |
| HP Neoview | TIMESTAMP(p), January 01, 0001 00:00:00.000000 to December 31, 9999 23:59:59.999999 (microseconds accuracy) |
| Ingres | TIMESTAMP(p), 0001-01-01 00:00:00 to 9999-12-31 24:00:00 |
Related Data Types in MySQL
All Data Types