Extracting Date and Time Information (Embedded SQL Guide)

Go Up to Querying the Database for Current Date and Time Information

The EXTRACT() function extracts date and time information from databases. EXTRACT() has the following syntax:

EXTRACT (part FROM value)

The value passed to the EXTRACT() expression must be DATE, TIME, or TIMESTAMP. Extracting a part that doesn’t exist in a data type results in an error. For example:

EXTRACT (TIME FROM aTime)

would be successful, while a statement such as:

EXTRACT (YEAR from aTIME)

would fail.

The data type of EXTRACT() expressions depends on the specific part being extracted:

Extract Part	Resulting data type	Representing
YEAR	SMALLINT	Year, range 0-5400
MONTH	SMALLINT	Month, range 1-12
DAY	SMALLINT	Day, range 1-31
HOUR	SMALLINT	Hour, range 1-23
MINUTE	SMALLINT	Minute, range 1-59
SECOND	DECIMAL(6,4)	Second, range 0-59.9999
WEEKDAY	SMALLINT	Day of the week, range 0-6 (0 = Sunday, 1 = Monday, and so on)
YEARDAY	SMALLINT	Day of the year, range 1-366