Date and Time Functions
Date and time functions operate on a date and time input value and return a string, numeric, or date and time value.
ADD_MONTHS
ADD_MONTHS(datetime, n)
Operand type: datetime can be an ANSIDATE, TIMESTAMP, CHAR, VARCHAR, NCHAR, or NVARCHAR; n is an integer.
Result type: Same as the first parameter
Adds the number, which represents months, to the date. If the number is negative then the date is reduced by the number of months.
Example:
SELECT ADD_MONTHS('2012-11-07',2)\g
2013-01-07
DATE_FORMAT
DATE_FORMAT
DATE_FORMAT(datetime, format)
TIME_FORMAT
TIME_FORMAT(datetime, format)
TIME_FORMAT is an alias for DATE_FORMAT.
Operand type: datetime is a DATE, TIME, or TIMESTAMP; format is a character string
Result type: VARCHAR
Returns datetime formatted according to the format string.
The specifiers in the following table can be used in the format string. The “%” character is required before format specifier characters. If the format specifier is inappropriate to the data type of datetime, then NULL is returned.
DATE_FORMAT('2010-10-03 22:23:00', '%W %M %Y')
returns 'Sunday October 2010'
DATE_FORMAT('2007-10-03 22:23:00', '%H:%i:%s')
returns '22:23:00'
DATE_FORMAT('1900-10-04 22:23:00', '%D %y %a %d %m %b %j')
returns '4th 00 Thu 04 10 Oct 277'
DATE_FORMAT('1997-10-04 22:23:00', '%H %k %I %r %T %S %w')
returns '22 22 10 10:23:00 PM 22:23:00 00 6'
DATE_FORMAT('1999-01-01', '%X %V')
returns '1998 52'
DATE_PART
DATE_PART(unit,date)
Result type: INTEGER
Returns an integer containing the specified unit component of the input date.
The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter is case sensitive.
Many of these units can also be derived using the EXTRACT function or using explicit, individual extract functions such as HOUR() or MILLISECOND().
The DATE_PART function is useful in set functions and in ensuring correct ordering in complex date manipulation. For example, if date_field contains the value 23-oct-2012, then:
DATE_PART('MONTH',DATE(date_field))
returns a value of 10 (representing October), and
DATE_PART('DAY',DATE(date_field))
returns a value of 23.
Months are numbered 1 to 12, starting with January. Hours are returned according to the 24-hour clock. Quarters are numbered 1 through 4.
Week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. However, if you specify ISO-Week, which is ISO 8601 compliant, the week begins on Monday, but the first week is the week that has the first Thursday. The weeks are numbered 1 through 53.
Therefore, if you are using Week and the date falls before the first Monday in the current year, date_part returns 0. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and DATE_PART returns either 52 or 53.
The following table illustrates the difference between Week and ISO-Week:
DATE_TRUNC
DATE_TRUNC(unit,date)
Operand type: date can be absolute ANSIDATE, TIME, TIMESTAMP
Result type: Same as operand type
Returns a date value truncated to the specified unit.
The following table lists valid unit parameters. A unit parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.
Where unit is DAY or greater, the day boundary is taken to be in the user's time zone. For example, the following query against a timestamp with time zone value:
SELECT DATE_TRUNC('DAY',TIMESTAMP '2011-02-03 16:12:13.000000000-08:00')
returns
2011-02-03 00:00:00.000000000-08:00
which retains the original time zone offset.
Use the DATE_TRUNC function to group all the dates within the same month or year, and so forth. For example:
DATE_TRUNC('MONTH',TIMESTAMP('1998-10-23 12:33:00'))
returns 1998-10-01, and
DATE_TRUNC('YEAR',DATE'1998-10-23')
returns 1998-01-01.
Truncation takes place in terms of calendar years and quarters (January 1, April 1, June 1, October 1).
To truncate in terms of a fiscal year, offset the calendar date by the number of months between the beginning of your fiscal year and the beginning of the next calendar year (6 mos for a fiscal year beginning July 1, or 4 mos for a fiscal year beginning September 1):
DATE_TRUNC('YEAR',date + INTERVAL '4' MONTH) - INTERVAL '4' MONTH
Weeks start on Monday. The beginning of a week for an early January date falls into the previous year.
DAY
DAY('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the day portion of a date or timestamp.
DAY(TIMESTAMP '2006-12-15 12:30:55.1234') returns 15
DAYOFMONTH
DAYOFMONTH
DAYOFMONTH(datetime)
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER.
Returns the day of the month from the specified datetime value.
DAYOFMONTH(DATE '2011-02-15') returns 15
DAYOFWEEK
DAYOFWEEK
DAYOFWEEK(datetime [,n])
Operand type: datetime is a DATE, TIME, or TIMESTAMP; n is an integer.
Result type: INTEGER
Returns the day of the week from the specified datetime value, where 1 = Sunday unless n is used to shift the start of week.
DAYOFWEEK(DATE '2011-02-15',4) returns 6
DAYOFWEEK(DATE '2011-02-15') returns 3
DAYOFYEAR
DAYOFYEAR
DAYOFYEAR(datetime)
Returns the ordinal number of day in the year, in the range 1 to 366, for datetime.
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER
DAYOFYEAR(DATE '2011-02-04') returns 35
DOY
Same as DAYOFYEAR.
EXTRACT
EXTRACT (part FROM datetime)
Operand type: datetime can be a DATE, TIME, TIMESTAMP, or INTERVAL value expression.
Result type: INTEGER
Extracts a particular field from a datetime value. Part specifies the field to extract.
A part parameter must be specified using a quoted string (for example: 'YEAR'). The parameter and is case sensitive.
Valid values for part are:
YEAR
Year field. Range: 0 - 9999
MONTH
Month field. Range: 1 - 12
DAY
Day field. Range: 1 - 31
HOUR
Hour field. Range: 0 - 23
MINUTE
Minute field. Range: 0 - 59
SECOND
Second field. Range: 0 - 59
MILLISECONDS
Fractional seconds as milliseconds. Range: 0 - 999.
MICROSECONDS
Fractional seconds as microseconds. Range: 0 - 999.
NANOSECONDS
Fractional seconds as nanoseconds. Range: 0 - 999.
TIMEZONE_HOUR
Time zone hour offset. Range: -12 - 14.
TIMEZONE_MINUTE
Time zone minute offset. Range: 0 - 59
DAYOFWEEK
Day of week with Sunday=1. Range: 1 - 7.
DAYOFYEAR
Day of year. Range: 1 - 366.
ISO_WEEK
Week of year ISO 6801.
QUARTER
Year quarter. Range: 1 - 4.
WEEK
Week of year. Range: 1 - 53.
WEEK_ISO
Week of year ISO 6801.
Note: The datetime value cannot be an interval type on the TIMEZONE_HOUR, TIMEZONE_MINUTE, DAYOFWEEK, DAYOFYEAR, WEEK, ISO_WEEK, QUARTER, and EPOCH functions.
Examples:
SELECT EXTRACT (YEAR FROM datecol) FROM datetable;
SELECT EXTRACT (MONTH FROM CURRENT_DATE);
SELECT EXTRACT (HOUR FROM timecol) FROM datetable;
SELECT * FROM tx WHERE EXTRACT(HOUR FROM datetable) = 17;
FROM_UNIXTIME
Operand types: INTEGER, character string
Formats UNIX timestamp as a date.
FROM_UNIXTIME
FROM_UNIXTIME(i)
Result type: TIMESTAMP WITHOUT TIME ZONE
Returns TIMESTAMP WITHOUT TIME ZONE created from the specified integer, which must be a UNIX time (number of seconds since 1-Jan-1970).
FROM_UNIXTIME(i, format)
Result type: VARCHAR
Returns
i formatted in UNIX time, according to the specified
format. For valid formats, see
DATE_FORMAT.
FROM_UNIXTIME(1196440219)
returns 2007-11-30 10:30:19.000000
FROM_UNIXTIME(1196440219, '%Y %D %M %h:%i:%s %x')
returns 2007 30th November 10:30:19 2007
Note: Results from this function are from a GMT offset, which may result in a value that is from '1969-12-31 12:00:00.000000' depending on the actual time zone of the server.
HOUR
HOUR('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the hour portion of a time or timestamp.
HOUR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12
INTERVAL_DIFF
INTERVAL_DIFF('datetime1', 'datetime2')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTERVAL YEAR TO MONTH
Returns the difference between datetime1 and datetime2 expressed as an INTERVAL YEAR TO MONTH.
This complements subtraction of TIMESTAMPs that yields an INTERVAL DAY TO SECOND and subtraction of ANSIDATEs that yields an integer.
INTERVAL_DIFF('2010-1-1', '2015-3-1') returns 5-2
LAST_DAY
LAST_DAY(datetime)
Operand type: DATE, TIMESTAMP
Result type: DATE or TIMESTAMP depending on input
Returns the last day of the month in which the specified date or timestamp falls. Returns NULL if the argument is invalid.
LAST_DAY(DATE '2003-02-05')
returns 2003-02-28
LAST_DAY(DATE '2004-02-05')
returns 2004-02-29
LAST_DAY(TIMESTAMP '2004-01-01 01:01:01')
returns 2004-01-31
LAST_DAY(DATE '2003-03-32')
returns NULL
MICROSECOND
MICROSECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as microseconds.
MICROSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400
MILLISECOND
MILLISECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as milliseconds.
MILLISECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123
MINUTE
MINUTE('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the minute portion of a time or timestamp.
MINUTE(TIMESTAMP '2006-12-15 12:30:55.1234') returns 30
MONTH
MONTH('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the month portion of a date or timestamp.
MONTH(TIMESTAMP '2006-12-15 12:30:55.1234') returns 12
MONTHS_BETWEEN
MONTHS_BETWEEN
MONTH_BETWEEN(date1, date2)
Operand type: DATE, TIMESTAMP
Result type: FLOAT8
Returns the number of months between date1 and date2, positive if date2 precedes date1 and negative if date1 precedes date2. If the day of date1 is the same as the day in date2 or both dates are the last days of months, then the result is a whole number of months; otherwise, any time portion is also taken into consideration in the difference, where days are treated as 1/31 of a month.
SELECT MONTHS_BETWEEN(DATE‘2010-06-15’, DATE‘2011-06-15’);
-12.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-05-15’);
1.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-30’, DATE‘2011-05-31’);
1.000
SELECT MONTHS_BETWEEN(DATE‘2011-06-15’, DATE‘2011-06-01’);
0.452
NANOSECOND
NANOSECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER4
Extracts the fractions of seconds portion of a time or timestamp as nanoseconds.
NANOSECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 123400000
QUARTER
QUARTER('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the quarter of the calendar year that corresponds to the date or timestamp. Quarters are numbered 1 through 4.
QUARTER(TIMESTAMP '2006-12-15 12:30:55.1234') returns 4
ROUND(datetime)
ROUND(datetime [,str-interval])
Operand type: datetime is a DATE, TIME, or TIMESTAMP; str-interval is a character string
Result type: DATE, TIME, or TIMESTAMP
Returns datetime rounded up to the unit specified by str-interval. If str-interval is omitted, then datetime is rounded to the nearest day.
The str-interval can be any of the following:
Examples:
SELECT ROUND('2013-10-11','CC')\g
2000-01-01
SELECT ROUND('23:40:10.123456','MIN')\g
23:40:00
SELECT ROUND('2013-10-11 23:40:10.123456','DD')\g
2013-10-12 00:00:00.000000
SELECT ROUND('2013-10-11','Q')\g
2013-10-01
SELECT ROUND('2013-10-11 23:40:10.123456')\g
2013-10-12 00:00:00.000000
SECOND
SECOND('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the second portion of a time or timestamp.
SECOND(TIMESTAMP '2006-12-15 12:30:55.1234') returns 55
STR_TO_DATE
STR_TO_DATE(str,format)
Operand type: str and format are character strings
Result type: DATE, TIME, or TIMESTAMP
Returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the format string contains only a date or time part. If the DATE, TIME, or DATETIME value extracted from string str is illegal, the function returns NULL and produces a warning. This function is the inverse of the DATE_FORMAT() function.
The
format string can contain literal characters and format specifiers beginning with %. Literal characters in
format must match literally in
str. Format specifiers in
format must match a date or time part in
str. For the specifiers that can be used in
format, see
DATE_FORMAT.
CREATE TABLE teststr(str VARCHAR(20));
INSERT INTO teststr VALUES('01,6,2012');
SELECT STR_TO_DATE(str,'%d,%m,%Y') FROM teststr;
returns
2012-06-01
Note: This function works only for data stored in tables created with the X100 or X100_ROW storage structure.
TIMESTAMPADD
TIMESTAMPADD(interval, n, datetime)
Operand type: Integer, and DATE, TIME, TIMESTAMP
Result type: DATE, TIME, or TIMESTAMP
Returns the datetime after adding the specified number of intervals where:
interval is a keyword from the list: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.
n is an integer expression. The value can be positive or negative as needed.
datetime is a column or a datetime expression that can be a DATE, TIMESTAMP, or TIME value.
TIMESTAMPADD(YEAR, 5, '2010-10-05') returns 2015-10-5
TIMESTAMPDIFF
TIMESTAMPDIFF(interval, datetime1, datetime2)
Operand type: Integer, and DATE, TIME, TIMESTAMP
Result type: Integer type
Returns the integer number of intervals between the two datetimes where:
interval is one of the following keywords: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND, MILLISECOND, MICROSECOND or NANOSECOND. For compatibility, this keyword can be prefixed with SQL_TSI_.
datetime1, datetime2 are columns or datetime expressions whose difference is to be determined in terms of the specific interval, and where datetime1 is a start time, and datetime2 is an end time.
The result is an integer value which can also be negative if datetime1 chronologically follows datetime2.
TIMESTAMPDIFF(YEAR, '2012-01-01', '2008-01-01') returns ‑4
TRUNC
TRUNC(datetime [,str-interval])
TRUNCATE(datetime [,str-interval])
Operand type: datetime is a DATE, TIME, or TIMESTAMP.
Result type: DATE, TIME, or TIMESTAMP
Returns datetime truncated to the unit specified by str-interval. If str-interval is omitted, then datetime is truncated to the nearest day.
The str-interval can be any of the following:
The precision of the result is the default precision for the input data type. For example, if TRUNC operates on a TIME column, the precision returned is 0; if TIMESTAMP, then 6.
Examples:
SELECT TRUNC('2013-10-11','CC')\g
2000-01-01
SELECT TRUNC('2013-10-11 23:40:10.123456','DD')\g
2013-10-11 00:00:00.000000
SELECT TRUNC('23:40:10.123456','MIN')\g
23:40:00
SELECT TRUNC('2013-10-11 23:40:10.123456')\g
2013-10-11 00:00:00.000000
UNIX_TIMESTAMP
UNIX_TIMESTAMP()
UNIX_TIMESTAMP([datetime])
Operand type: DATE, TIME, or TIMESTAMP
Result type: INTEGER
Returns a UNIX timestamp (number of seconds since 1 Jan 1970) for the current time (if no argument is specified) or for the specified date.
UNIX_TIMESTAMP(TIMESTAMP '2007-11-30 10:30:19') returns 1196440219
Note: This function returns a valid result up to the year 2038 because the result is always an integer4 value in the range 1 though MAX_I4.
WEEK
WEEK(date [,mode])
Operand types: date is a DATE, TIMESTAMP. The optional mode is an integer.
Result type: INTEGER2
Returns the week number for date.
If the mode parameter is omitted, week 1 begins on the first Monday of the year. Dates before the first Monday of the year are considered to be in week 0. Weeks are numbered 0 to 53 for the single parameter version. (Corresponds to mode 5 in the following table.)
The optional mode parameter lets you specify: the starting day of the week (Sunday or Monday); the definition of the first week of the year; and whether any days before the "first week of the year" are in week 0 or in the last week of the prior year.
The mode values are:
WEEK(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50
WEEK(DATE '2008-02-20',1) returns 8
WEEK(DATE '2008-12-31',1) returns 53
WEEK_ISO
WEEKISO('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER1
Extracts the number of the week of the year that the date or timestamp refers to, and conforms to ISO 8601 definition for number of the week. Week_iso begins on Monday, but the first week is the week that has the first Thursday of the year. If you are using ISO-Week and the date falls before the week containing the first Thursday of the year, that date is considered part of the last week of the previous year, and date_part returns either 52 or 53.
WEEK_ISO is equivalent to WEEK() function with mode 3.
WEEK_ISO(TIMESTAMP '2006-12-15 12:30:55.1234') returns 50
YEAR
YEAR('datetime')
Operand type: DATE, TIME, TIMESTAMP
Result type: INTEGER2
Extracts the year portion of a date or timestamp.
YEAR(TIMESTAMP '2006-12-15 12:30:55.1234') returns 2006
YEARWEEK
YEARWEEK(date [,mode])
Operand types: date is a DATE or TIMESTAMP. The optional mode is an integer.
Result type: INTEGER4
Returns year and week for date.
The optional mode parameter lets you specify whether the week starts on Sunday or Monday.
Values for the optional mode parameter are shown under WEEK Function.
If the mode parameter is omitted, mode 0 is assumed (so that weeks start on Sunday, and week 1 is the first week with a Sunday; dates before the first Sunday of the year are assumed to be in the last week of the prior year).
Note: YEARWEEK does not return a week as week 0. The modes that would normally return dates early in the year, before the start of week 1, will compute the week as if the given date were an extension of the previous year. That is, for YEARWEEK, mode 0 operates identically to mode 2; similarly mode 1 becomes 3, mode 4 becomes 6, and mode 5 becomes 7.
For example: 1 January 2000 is a Saturday and is before the first week of the year, regardless of the mode selected. Therefore, it is treated as being in the last week of 1999. Since the last week of 1999 is week 52 (for all modes), YEARWEEK('2000-01-01',mode) returns 199952 (for all modes).
YEARWEEK(DATE '1987-01-01')
returns 198653
YEARWEEK(DATE '2000-01-01,4')
returns 200001
YEARWEEK(DATE '2000-01-01',3)
returns 199952