Skip to content
≡

Administrator

Developer

Tools

External Tools

Datetime Functions

Datetime Functions#

These functions perform tasks on entered date and time values and return character, numeric or date/time type values. If a value of an input argument is of the character data type, the value must be entered in the format set in the ALTIBASE_DATE_FORMAT environment variable or the DEFAULT_DATE_FORMAT property (the former takes precedence). Additionally, this format is also used to display date/time values that are returned when these functions are executed at the iSQL prompt.

For more information on the DATE data type and the datetime format model, which is used when returning date type data, please refer to the General Reference.

ADD_MONTHS#

Syntax#

ADD_MONTHS (date, number)

Description#

ADD_MONTHS adds the value of the number months to the value of date and returns the result. The number argument can be an integer or any value that can be implicitly converted to an integer. If the value of the date is the last day of a month, it returns the last day of the month after adding the value of number.

Example#

<Query> Display the date of joining an employee with the employee number 10 and the date six months after joining.

SELECT join_date
     , ADD_MONTHS(join_date, 6)
  FROM employees
 WHERE eno = 10;

JOIN_DATE    ADD_MONTHS(JOIN_DATE, 6)
-----------------------------------------
05-JAN-2010  05-JUL-2010
1 row selected.

<Query> Display the date of joining an employee with employee number 20 and the date six months after joining.

SELECT join_date
     , ADD_MONTHS(join_date, 6)
  FROM employees
 WHERE eno = 20;

JOIN_DATE    ADD_MONTHS(JOIN_DATE, 6)
-----------------------------------------
28-FEB-2010  31-AUG-2010
1 row selected.

CONV_TIMEZONE#

Syntax#

CONV_TIMEZONE(expr, src_tz, dest_tz)

Description#

CONV_TIMEZONE converts the expr based on the src_tz time zone to the dest_tz time zone. Time zone region names, abbreviations or UTC offset values are valid for src_tz and dest_tz.

Example#

<Query> Convert the current time based on the UTC offset +00:00 to the KST time zone, then compare it with the output of the SYSDATE function. The outputs will be identical since the time zone of the database server is KST.

SELECT TO_CHAR(CONV_TIMEZONE(UNIX_DATE, '+00:00', 'KST'), 'MM/DD HH:MI') kst_date
  FROM DUAL;

KST_DATE
--------------------------------------
06/12 17:27
1 row selected.

SELECT TO_CHAR(SYSDATE, 'MM/DD HH:MI')
  FROM DUAL;

TO_CHAR(SYSDATE, 'MM/DD HH:MI')
--------------------------------------
06/12 17:27
1 row selected.

CURRENT_DATE#

Syntax#

CURRENT_DATE

Description#

CURRENT_DATE returns the current date and time of the operating system on which Altibase is running based on the time zone of the client session.

Example#

<Query> Output the current date of the system based on the time zone of the client.

SELECT TO_CHAR(CURRENT_DATE,'YYYY MM/DD HH:MI') current_date
  FROM DUAL;

CURRENT_DATE
-----------------------------------------------------
2013 06/12 15:33
1 row selected.

CURRENT_TIMESTAMP#

Syntax#

CURRENT_TIMESTAMP

Description#

CURRENT_TIMESTAMP returns the current date and time of the operating system on which Altibase is running based on the time zone of the client session.

This function is an alias of the CURRENT_DATE function.

Example#

<Query> Output the current date of the system based on the time zone of the client.

SELECT TO_CHAR(CURRENT_TIMESTAMP,'YYYY MM/DD HH:MI') current_timestamp
  FROM DUAL;

CURRENT_TIMESTAMP
-----------------------------------------------------
2013 06/12 15:34
1 row selected.

DATEADD#

Syntax#

DATEADD (date, number, date_field_name)

Description#

DATEADD increases the element of date that is specified using date_field_name by the amount specified using n. If n is a non-integer, the places after the decimal point are first discarded (i.e. the value is truncated), and then the operation is carried out.

If the value of date_field_name is 'SECOND', n (that is, the number of seconds) cannot exceed the equivalent of 68 years. If it is 'MICROSECOND', n cannot exceed the equivalent of 30 days.

The following table shows the date_field_name values that can be used with the DATEADD function, and the result in each case:

Date Field Name Description
CENTURY The year portion of date is increased by n*100.
YEAR The year portion of date is increased by n.
QUARTER The month portion of date is increased by n*3.
MONTH The month portion of date is increased by n.
WEEK The day portion of date is increased by n*7
DAY The day portion of date is increased by n.
HOUR The hour portion of date is increased by n.
MINUTE The minute portion of date is increased by n.
SECOND The second portion of date is increased by n.
MICROSECOND The microsecond portion of date is increased by n

Example#

<Query> Get the number of employees who were hired less than 40 days ago.

SELECT COUNT(*)
  FROM employees
 WHERE join_date > DATEADD (SYSDATE, -40, 'DAY');

COUNT
----------
5
1 row selected.

DATEDIFF#

Syntax#

DATEDIFF (startdate, enddate, date_field_name)

Example#

DATEDIFF returns the difference between enddate and startdate (i.e. enddate - startdate) in the units specified using date_field_name. If startdate is greater than enddate, a negative number is returned.

This function first determines the values of enddate and startdate in the units specified using date_field_name, and then returns the difference. This function always returns an integer. Noninteger results are truncated, that is, rounded down.

The possible values for date_field_name with DATEDIFF, that is, the units in which the resultant value can be returned, are as follows:

  • CENTURY

  • YEAR

  • QUARTER

  • MONTH

  • WEEK

  • DAY

  • HOUR

  • MINUTE

  • SECOND

  • MICROSECOND

The range of values that the DATEDIFF function can return is limited for particular values of date_field_name. If the value of date_field_name is 'SECOND', the difference between enddate and startdate cannot exceed 68 years. If it is 'MICROSECOND', the difference between enddate and startdate cannot exceed 30 days.

The result returned by this function is in the BIG INTEGER data type.

Example#

<Query> Get the difference between August 31, 2005 and November 30, 2005 in months.

SELECT DATEDIFF ('31-AUG-2005', '30-NOV-2005', 'MONTH')
  FROM DUAL;

DATEDIFF ('31-AUG-2005', '30-NOV-2005',  
-------------------------------------------
3                    
1 row selected.

DATENAME#

Syntax#

DATENAME (date, date_field_name)

Description#

Depending on the input date_field_name value, DATENAME returns the name of the month or weekday for the specified date.

The following table shows the values of date_field_name that can be used with the DATENAME function:

Date Field Name Description
MONTH, Month, month The month (unabbreviated)
MON, Mon, mon The month (abbreviated)
DAY, Day, day The day of the week (unabbreviated)
DY, Dy, dy The day of the week (abbreviated)

The possible return values for each value of date_field_name are as shown below. date_field_name can be entered in uppercase, lowercase, or title case (i.e. first character capitalized). The case of the output will match that of date_field_name.

  • MONTH
    JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER

  • MON
    JAN, FEB, MAR, APR, MAY, JUN, JUL, AUG, SEP, OCT, NOV, DEC

  • DAY
    SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY

  • DY
    SUN, MON, TUE, WED, THU, FRI, SAT

Example#

<Query> Output the month of Dec. 28, 1980.

SELECT DATENAME ('28-DEC-1980', 'Month') FROM DUAL;

DATENAME ('28-DEC-1980', 'Month')  
-------------------------------------
December    
1 row selected.

DB_TIMEZONE#

Syntax#

DB_TIMEZONE()

Description#

DB_TIMEZONE returns the time zone set for the database server. The return value takes the format of time zone region names, abbreviations or UTC offset values.

Example#

<Query> Output the time zone set for the database server.

SELECT DB_TIMEZONE() FROM DUAL;

DB_TIMEZONE
--------------------------------------------
+09:00
1 row selected.

EXTRACT, DATEPART#

Syntax#

EXTRACT (date, date_field_name)
DATEPART (date, date_field_name)

Description#

These functions, which are identical, return the value corresponding to date_field_name for the input date.

Date Field Name Description
CENTURY The current century (for example, returns "21" for "2011", or "20" for "1999")
YEAR Year
QUARTER Quartar
MONTH Month
WEEK Returns the week of the year. (The first Saturday of the year and any preceding days are in the first week of the year, meaning that the first "week" might be only one day long.)
WEEKOFMONTH Returns the week of the month. (The first Saturday of the month and any preceding days are in the first week of the month, meaning that the first "week" might be only one day long.)
DAY Day
DAYOFYEAR Returns the day of the year
DAYOFWEEK Returns the day of the week. (Sunday = 1)
HOUR Hour
MINUTE Minute
SECOND Second
MICROSECOND Microsecond

Example#

<Query> Find the quarter in which employee number 10 was hired.

SELECT DATEPART (join_date, 'QUARTER')
  FROM employees
 WHERE eno = 10;

DATEPART (JOIN_DATE, 'QUARTER')
----------------------------------
1
1 row selected.

MONTHS_BETWEEN#

Syntax#

MONTHS_BETWEEN (date1, date2)

Description#

MONTHS_BETWEEN subtracts date2 from date1 and returns the result in months. If date1 is less than date2, a negative number will be returned.

If date1 and date2 are the same day of the same month, the same day of different months, or the respective last days of different months, an integer will be returned.

In these cases, the hour, minute, second and microsecond portions of date1 and date2 are ignored. In other cases, the return value is not an integer, and the fractional part is determined by converting the hour, minute, second and microsecond portions of the input dates to months, on the assumption that there are 31 days in every month.

The return type is DOUBLE.

Example#

<Query> Get the difference between February 2, 1995 and January 1, 1995 in months.

SELECT MONTHS_BETWEEN (TO_DATE('02-02-1995','MM-DD-YYYY'), TO_DATE('01-01-1995','MM-DD-YYYY') ) months
  FROM DUAL;

MONTHS                 
-------------------------
1.03225806451613      
1 row selected.

ROUND#

Syntax#

ROUND (date [, date_field_name])

Description#

ROUND returns a date that has been rounded to the nearest unit specified using date_field_name. The default value of date_field_name is 'DAY'.

The following table shows the values of date_field_name that can be used with the ROUND function:

Date Field Name Description
CENTURY
SCC
CC		 Returns the first day in the nearest century. Dates in the year xx51 and beyond are rounded up to the next century. (Note that a century begins in the year xxx1, e.g. 2001.)
YEAR
SYYYY
YYYY
YYY
YY
Y		 Dates from July 1 and onward are rounded up to the next year.
QUARTER
Q		 Returns the first day in the nearest quarter. The 16th day of the second month and subsequent dates are rounded up to the next quarter.
MONTH
MON
MM
RM		 The 16th and subsequent dates are rounded up to the next month.
WEEK
WW		 Returns the nearest Sunday. Thursdays are rounded up; Wednesdays are rounded down.
DAY
DDD
DD
J		 Times after 12:00 PM are rounded to the following day
HOUR
HH
HH12
HH24		 Times 30 minutes after the hour are rounded to the next hour.
MINUTE
MI		 Times 30 seconds after the minute are rounded to the next minute.

Example#

<Query> Round December 27, 1980 to the nearest new year and output the result.

SELECT ROUND ( TO_DATE('27-DEC-1980', 'DD-MON-YYYY'), 'YEAR')
  FROM DUAL;

ROUND ( TO_DATE('27-DEC-1980', 'DD-MON-Y
-------------------------------------------
1981/01/01 00:00:00
1 row selected.

LAST_DAY#

Syntax#

LAST_DAY (date)

Description#

LAST_DAY returns the last day of the month that contains date. The return type is always DATE, regardless of whether the type of date is DATE, CHAR or VARCHAR.

Examples#

<Query> Display the last day in December 2001. 

SELECT LAST_DAY(TO_DATE('15-DEC-2001')) FROM DUAL;

LAST_DAY(TO_DATE('15-DEC-2001')) 
-----------------------------------
2001/12/31 00:00:00  
1 row selected.

<Query> Display the last day of the month in which each employee was hired.

SELECT LAST_DAY(join_date ) FROM employees;

LAST_DAY(JOIN_DATE ) 
-----------------------

1999/11/30 00:00:00  
2000/01/31 00:00:00
.
.
. 
20 rows selected.

NEXT_DAY#

Syntax#

NEXT_DAY (date, weekday)

Description#

The return type is always DATE, regardless of whether the type of date is DATE, CHAR or VARCHAR. NEXT_DAY returns the date of the first weekday, specified using weekday, that is later than date. If the day of the week of date is the same as weekday, the same weekday the following week is returned.

The weekday argument must be one of the days of the week. It is not case-sensitive, and can be either the entire weekday or the first three characters of the weekday.

Example#

<Query> For each employee, display the hiring date and the date of the following Sunday.

SELECT join_date
     , NEXT_DAY(join_date, 'SUNDAY') first_sunday
  FROM employees;

JOIN_DATE            FIRST_SUNDAY         
---------------------------------------------
.
.
.
24-JAN-2004  25-JAN-2004
29-NOV-2009  06-DEC-2009
14-JUN-2010  20-JUN-2010
05-JAN-2010  10-JAN-2010
.
.
.
20 rows selected.

SESSION_TIMEZONE#

Syntax#

SESSION_TIMEZONE()

Description#

This function returns time zone specified in the database session. The return value is the time zone, location name, abbreviation or UTC off set form.

Example#

<Query> Output the time zone specified in the database session.

SELECT SESSION_TIMEZONE() FROM DUAL;

SESSION_TIMEZONE                               
--------------------------------------------
+09:00                                    
1 row selected.

SYSDATE#

Syntax#

SYSDATE

Description#

SYSDATE returns the current date and time set for the operating system on which Altibase is installed.

Example#

<Query> Display the system date.

SELECT SYSDATE system_date FROM DUAL;

SYSTEM_DATE          
-----------------------
2005/01/20 09:49:33  
1 row selected.

SYSTIMESTAMP#

Syntax#

SYSTIMESTAMP

Description#

This output the date in the current system. It is an alias of the SYSDATE function. It does not support time zones.

Example#

<Query> Output the system date (i.e. the current date).

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-----------------------
2005/01/20 09:49:33  
1 row selected.

SYSDATETIME#

Syntax#

SYSDATETIME

Description#

This prints the current date and time of the system of which Altibase is running. This is an alias of the SYSDATE function and does not support time zones.

Example#

<Query> Display stem date (current date).

SELECT SYSDATETIME FROM DUAL;

SYSDATETIME  
---------------
2005/01/20 09:49:33  
1 row selected.

TRUNC (date)#

Syntax#

TRUNC (date [, 'fmt'])

Description#

This is a discard function.

TRUNC returns date truncated to the unit specified using fmt. In other words, it replaces the value of all units below the unit specified using fmt with zeros (0s).

If fmt is omitted, then the time portion of the day is removed, that is, date is truncated to the beginning of date.

In addition to the units supported by ROUND, the following units can be used for the TRUNC function. For the unit supported by ROUND, refer to the table provided by ROUND function.

  • SECOND

  • MICROSECOND

Examples#

<Query> Round off the time portion of the system time and return the resultant date. 

SELECT TRUNC(SYSDATE) FROM DUAL;    

TRUNC(SYSDATE)       
-----------------------
2005/07/19 00:00:00  
1 row selected.

<Query> Round off the date portion of the input date and return the resultant year.

SELECT TRUNC(TO_DATE('2005-JUL-19','YYYY-MON-DD'), 'YEAR') new_year
  FROM DUAL;

NEW_YEAR             
-----------------------
2005/01/01 00:00:00  
1 row selected.

UNIX_DATE#

Syntax#

UNIX_DATE

Description#

UNIX_DATE outputs the current date and time of the operating system on which Altibase is running based on the time zone of UTC +00:00.

Example#

<Query> Output the current date of the system.

SELECT TO_CHAR(UNIX_DATE,'YYYY MM/DD HH:MI') unix_date
  FROM DUAL;

UNIX_DATE
-----------------------------------------------------------
2013 06/12 06:32
1 row selected.

UNIX_TIMESTAMP#

Syntax#

UNIX_TIMESTAMP

Description#

UNIX_TIMESTAMP outputs the current date and time of the operating system on which Altibase is running based on the time zone of UTC +00:00.

This function is an alias of the UNIX_DATE function.

Example#

<Query> Output the current date of the system.

SELECT TO_CHAR(UNIX_TIMESTAMP,'YYYY MM/DD HH:MI') unix_timestamp
  FROM DUAL;

UNIX_TIMESTAMP
-----------------------------------------------------
2013 06/12 06:33
1 row selected.