3. Understanding SQL Data Types : SQL Data Types : Date and Time Data Types : Summary of ANSI Date/Time Data Types
 
Share this page                  
Summary of ANSI Date/Time Data Types
The following table summarizes valid input and output formats for ANSI date/time data types:
Data Type
Format
Example
ANSIDATE
yyyy-mm-dd
2007-02-28
TIME WITH TIME ZONE
hh:mm:ss.ffff... [+|-]th:tm
12:45:12.23456 -05:00
TIME or TIME WITHOUT TIME ZONE
hh:mm:ss.ffff...
12:45:12.23456
TIME WITH LOCAL TIME ZONE
hh:mm:ss.ffff...
12:45:12.23456
TIMESTAMP WITH TIME ZONE
yyyy-mm-dd hh:nn:ss.ffff... [+|-]th:tm
2007-02-28 12:45:12.23456 -05:00
TIMESTAMP or TIMESTAMP WITHOUT TIME ZONE
yyyy-mm-dd hh:nn:ss.ffff...
2007-02-28 12:45:12.23456
TIMESTAMP WITH LOCAL TIME ZONE
yyyy-mm-dd hh:nn:ss.ffff...
2007-02-28 12:45:12.23456
INTERVAL YEAR TO MONTH
[+|-]years-mm
55-4
INTERVAL DAY TO SECOND
[+|-]days hh:nn:ss.ffff...
-18 12:02:23.12345
where:
yyyy
Is a four-digit year value. All four digits are required.
mm
Is a two-digit month value between 01 to 12.
dd
Is a two-digit day value between 01 to 31.
hh
Is a two-digit hour value between 00 to 23.
nn
Is a two-digit minute value between 00 to 59.
th
Is a two-digit hour value between -12 to +14.
tm
Is a two-digit minute value between 00 to 59.
years
Is the number of years. Maximum of 9999.
days
Is the number of days. Maximum of 3652047.
ss
Is a two-digit seconds value between 0 and 59.
ffff...
Is 1 to 9 digits fractions of a second.