4. Elements of OpenSQL Statements : Functions : Scalar Functions : Date Functions
 
Share this page                  
Date Functions
OpenSQL supports functions that derive values from absolute dates and from interval dates. These functions operate on columns that contain date values. An additional function, dow(), returns the day of the week (mon, tue, and so on) for a specified date. For a description of the dow() function, see Data Type Conversion Functions (see page Data Type Conversion Functions).
Some date functions require you to specify a unit parameter; unit parameters must be specified using a quoted string. The following table lists valid unit parameters:
Date Portion
How Specified
Second
second, seconds, sec, secs
Minute
minute, minutes, min, mins
Hour
hour, hours, hr, hrs
Day
day, days
Week
week, weeks, wk, wks
ISO-Week
iso-week, iso-wk
Month
month, months, mo, mos
Quarter
quarter, quarters, qtr, qtrs
Year
year, years, yr, yrs
The following table lists the date functions:
Name
Format (Result)
Description
date_trunc(unit,date)
date
Returns a date value truncated to the specified unit.
date_part(unit,date)
integer
Returns an integer containing the specified (unit) component of the input date.
date_gmt(date)
any character data type
Converts an absolute date into the Greenwich Mean Time character equivalent with the format yyyy_mm_dd hh:mm:ss GMT. If the absolute date does not include a time, the time portion of the result is returned as 00:00:00.
For example, the query:
select date_gmt('1-1-98 10:13 PM PST')
returns the following value:
1998_01_01 06:13:00 GMT
while the query:
select date_gmt('1-1-1998')
returns:
1998_01_01 00:00:00 GMT
gmt_timestamp(s)
any character data type
Returns a twenty-three-character string giving the date s seconds after January 1, 1970 GMT. The output format is 'yyyy_mm_dd hh:mm:ss GMT'.
For example, the query:
select (gmt_timestamp (1234567890))
returns the following value:
2009_02_13 23:31:30 GMT
while the query:
(II_TIMEZONE_NAME = AUSTRALIA_ QUEENSLAND)
select date(gmt_timestamp (1234567890))
returns:
14-feb-2009 09:31:30
interval (unit,date_interval)
float
Converts a date interval into a floating-point constant expressed in the unit of measurement specified by unit. The interval function assumes that there are 30.436875 days per month and 365.2425 days per year when using the mos, qtrs, and yrs specifications.
For example, the query:
select(interval('days', '5 years'))
returns the following value:
1826.213
This function is not supported for the Oracle and MS SQL Enterprise Access products.
_date(s)
any character data type
Returns a nine-character string giving the date s seconds after January 1, 1970 GMT. The output format is dd-mmm-yy.
For example, the query:
select _date(123456)
returns the following value:
2-jan-70
_date4(s)
any character data type
Returns an eleven-character string giving the date s seconds after January 1, 1970 GMT. The output format is controlled by the II_DATE_FORMAT setting.
For example, with II_DATE_FORMAT set to US, the query:
select _date4(123456)
returns the following value:
02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
select _date4(123456)
returns this value:
02/01/1970
_time(s)
any character data type
Returns a five-character string giving the time s seconds after January 1, 1970 GMT. The output format is hh:mm (seconds are truncated).
For example, the query:
select _time(123456)
returns the following value:
02:17