SAND CDBMS SQL Reference Guide
Value Expressions

Functions

Date/Time Functions

The date/time functions are used to manipulate DATE, TIME, and TIMESTAMP values, and can appear wherever a value expression is permitted. The following date/time functions are supported:

DATE ( month, day, year )
This form of the DATE function returns the equivalent DATE data type value for the specified month, day, and year. The input values must be integers, and they must represent a valid date.

(The other DATE function is used to convert one input value to the DATE data type.)

Example

Input	Output
12, 31, 2003	2003-12-31

CREATE TABLE emp_data (emonth INT, eday INT, eyear INT);
INSERT INTO emp_data VALUES (12, 31, 2003);
1 row affected
SELECT DATE(emonth, eday, eyear) FROM emp_data;
1 row selected
1
----------
2003-12-31


DAY ( value-expression )
Returns the day portion of a DATE or TIMESTAMP value as an INTEGER value.

Examples

Input	Output
'11/27/1969'	27
'2004-02-13-14.14.59.624000'	13

CREATE TABLE dt_table (date_col DATE, timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('11/27/1969', '2004-02-13-14.14.59.624000');
1 row affected
SELECT date_col, DAY(date_col) AS day FROM dt_table;
1 row selected
date_col   day
---------- -----------
1969-11-27          27
SELECT timestamp_col, DAY(timestamp_col) AS day FROM dt_table;
1 row selected
timestamp_col              day
-------------------------- -----------
2004-02-13-14.14.59.624000          13


EXTRACT ( component FROM value-expression )
Returns the specified component from the input date/time expression as an INTEGER value. The possible components are the following:

• DAY
• MONTH
• YEAR
• HOUR
• MINUTE
• SECOND
• MICROSECOND

Note that DATE components cannot be extracted from TIME values, and vice versa.

Examples

Input	Output
DAY FROM '2003-12-29'	29
MONTH FROM '2003-12-29'	12
YEAR FROM '2004-02-17-14.20.06.131000'	2004
HOUR FROM '02:20:34'	2
MINUTE FROM '2004-02-17-14.20.06.131000'	20
SECOND FROM '02:20:34'	34
MICROSECOND FROM '2004-02-17-14.20.06.131000'	131000

CREATE TABLE dt_table (date_col DATE, timestamp_col TIMESTAMP, time_col TIME);
INSERT INTO dt_table VALUES ('2003-12-29', '2004-02-17-14.20.06.131000', '02:20:34');
1 row affected
SELECT date_col, EXTRACT(DAY FROM date_col) AS result FROM dt_table;
1 row selected
date_col   result
---------- -----------
2003-12-29          29
SELECT date_col, EXTRACT(MONTH FROM date_col) AS result FROM dt_table;
1 row selected
date_col   result
---------- -----------
2003-12-29          12
SELECT timestamp_col, EXTRACT(YEAR FROM timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-14.20.06.131000        2004
SELECT time_col, EXTRACT(HOUR FROM time_col) AS result FROM dt_table;
1 row selected
time_col result
-------- -----------
02:20:34           2
SELECT timestamp_col, EXTRACT(MINUTE FROM timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-14.20.06.131000          20
SELECT time_col, EXTRACT(SECOND FROM time_col) AS result FROM dt_table;
1 row selected
time_col result
-------- -----------
02:20:34          34
SELECT timestamp_col, EXTRACT(MICROSECOND FROM timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-14.20.06.131000      131000


HOUR ( value-expression )
Returns the hours portion of a TIME or TIMESTAMP value as an INTEGER value.

Examples

Input	Output
'15:08:37'	15
'2004-02-17-15.08.37.588000'	15

CREATE TABLE dt_table (time_col TIME, timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('15:08:37', '2004-02-17-15.08.37.588000');
1 row affected
SELECT time_col, HOUR(time_col) AS result FROM dt_table;
1 row selected
time_col result
-------- -----------
15:08:37          15
SELECT timestamp_col, HOUR(timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-15.08.37.588000          15


MICROSECOND ( value-expression )
Returns the microseconds portion of a TIMESTAMP value as an INTEGER value.

Example

Input	Output
'2004-02-17-15.08.37.588000'	588000

CREATE TABLE dt_table (timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('2004-02-17-15.08.37.588000');
1 row affected
SELECT timestamp_col, MICROSECOND(timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-15.08.37.588000      588000


MINUTE ( value-expression )
Returns the minutes portion of a TIME or TIMESTAMP value as an INTEGER value.

Examples

Input	Output
'15:08:37'	8
'2004-02-17-15.08.37.588000'	8

CREATE TABLE dt_table (time_col TIME, timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('15:08:37', '2004-02-17-15.08.37.588000');
1 row affected
SELECT time_col, MINUTE(time_col) AS result FROM dt_table;
1 row selected
time_col result
-------- -----------
15:08:37           8
SELECT timestamp_col, MINUTE(timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-15.08.37.588000           8


MONTH ( value-expression )
Returns the month portion of a DATE or TIMESTAMP value as an INTEGER value.

Examples

Input	Output
'02/17/2004'	2
'2004-02-17-15.08.37.588000'	2

CREATE TABLE dt_table (date_col DATE, timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('02/17/2004', '2004-02-17-15.08.37.588000');
1 row affected
SELECT date_col, MONTH(date_col) AS result FROM dt_table;
1 row selected
date_col   result
---------- -----------
2004-02-17           2
SELECT timestamp_col, MONTH(timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-15.08.37.588000           2


SECOND ( value-expression )
Returns the seconds portion of a TIME or TIMESTAMP value as an INTEGER value.

Examples

Input	Output
'15:08:37'	37
'2004-02-17-15.08.37.588000'	37

CREATE TABLE dt_table (time_col TIME, timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('15:08:37', '2004-02-17-15.08.37.588000');
1 row affected
SELECT time_col, SECOND(time_col) AS result FROM dt_table;
1 row selected
time_col result
-------- -----------
15:08:37          37
SELECT timestamp_col, SECOND(timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-15.08.37.588000          37


TODAY ( )
Returns the current date as a DATE value. (The same functionality is provided by the special constant CURRENT DATE.)

Example

CREATE TABLE dt_table (col1 DATE);
INSERT INTO dt_table VALUES (TODAY());
1 row affected
SELECT * FROM dt_table;
1 row selected
COL1
----------
2004-02-17


YEAR ( value-expression )
Returns the year portion of a DATE or TIMESTAMP value as an INTEGER value.

Examples

Input	Output
'02/17/2004'	2004
'2004-02-17-15.08.37.588000'	2004

CREATE TABLE dt_table (date_col DATE, timestamp_col TIMESTAMP);
INSERT INTO dt_table VALUES ('02/17/2004', '2004-02-17-15.08.37.588000');
1 row affected
SELECT date_col, YEAR(date_col) AS result FROM dt_table;
1 row selected
date_col   result
---------- -----------
2004-02-17        2004
SELECT timestamp_col, YEAR(timestamp_col) AS result FROM dt_table;
1 row selected
timestamp_col              result
-------------------------- -----------
2004-02-17-15.08.37.588000        2004