EXTRACT

You can use the EXTRACT built-in function can use to extract specific information from date and time values.

Syntax

EXTRACT( infoType FROM dateExpr );

infoType

The value (information) that you want to extract and return from the date-time expression. This can be one of the following values:

YEAR

The four-digit year value is extracted from the date-time expression.

QUARTER

The single digit (1-4) quarter number is extracted from the date-time expression.

MONTH

The month number (1-12) is extracted from the date-time expression.

MONTHNAME

The full month name (e.g. September) is extracted from the date-time expression.

WEEK

The week-of-year number (1 is the first week) is extracted from the date-time expression.

WEEKDAY

The day-of-week number (1-7, with Monday as 1 and Sunday as 7) is extracted from the date-time expression.

WEEKDAYNAME

The day-of-week name (e.g. Tuesday)  is extracted from the date-time expression.

DAYOFYEAR

The numeric day-of-year (0-366) is extracted from the date-time expression.

DAY

The numeric day-of-month (0-31) is extracted from the date-time expression.

HOUR

The numeric hour (0-23) is extracted from the date-time expression.

Note that Splice Machine  DATE values do not include time information and will not work correctly with this infoType.

MINUTE

The numeric minute (0-59) is extracted from the date-time expression.

Note that Splice Machine  DATE values do not include time information and will not work correctly with this infoType.

SECOND

The numeric second (0-59) is extracted from the date-time expression.

Note that Splice Machine  DATE values do not include time information and will not work correctly with this infoType.

dateExpr

The date-time expression from which you wish to extract information.

Note that Splice Machine  DATE values do not include time information and thus will not produce correct values if you specify HOUR, MINUTE, or SECOND infoTypes.

Examples

splice> SELECT Birthdate,
   EXTRACT (Quarter FROM Birthdate) "Quarter",
   EXTRACT (Week FROM Birthdate) "Week",
   EXTRACT(WeekDay FROM Birthdate) "Weekday"
   FROM Players
   WHERE ID < 20
   ORDER BY "Quarter";
BIRTHDATE |Quarter    |Week       |Weekday
----------------------------------------------
1987-03-27|1          |13         |5
1987-01-21|1          |4          |3
1991-01-15|1          |3          |2
1982-01-05|1          |1          |2
1990-03-22|1          |12         |4
1989-01-01|1          |52         |7
1988-04-20|2          |16         |3
1983-04-13|2          |15         |3
1990-06-16|2          |24         |6
1984-04-11|2          |15         |3
1981-07-02|3          |27         |4
1977-08-30|3          |35         |2
1989-08-21|3          |34         |1
1984-09-21|3          |38         |5
1990-10-30|4          |44         |2
1983-12-24|4          |51         |6
1983-11-06|4          |44         |7
1982-10-12|4          |41         |2
1989-11-17|4          |46         |5

19 rows selected

splice> values EXTRACT(monthname FROM '2009-09-02 11:22:33.04');
1
--------------
September


splice> values EXTRACT(weekdayname FROM '2009-11-07 11:22:33.04');
1
--------------
Saturday
1 row selected

splice> values EXTRACT(dayofyear FROM '2009-02-01 11:22:33.04');
1
-----------
32
1 row selected

splice> values EXTRACT(hour FROM '2009-07-02 11:22:33.04');
1
-----------
11
1 row selected

splice> values EXTRACT(minute FROM '2009-07-02 11:22:33.04');
1
-----------
22
1 row selected

splice> values EXTRACT(second FROM '2009-07-02 11:22:33.04');
1
-----------
33

1 row selected

See Also