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

Advance To:

Selecting Dates and Times