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 date functions are as follows:
DATE_TRUNC
date_trunc(unit,date)
Result Type: date
Returns a date value truncated to the specified unit.
DATE_PART
date_part(unit,date)
Result Type:integer
Returns an integer containing the specified (unit) component of the input date.
DATE_GMT
date_gmt(date)
Result Type: 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.
select date_gmt('1-1-98 10:13 PM PST')
returns 1998_01_01 06:13:00 GMT, while
select date_gmt('1-1-1998')
returns 1998_01_01 00:00:00 GMT
GMT_TIMESTAMP
gmt_timestamp(s)
Result Type: 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'.
select (gmt_timestamp (1234567890))
returns the following value 2009_02_13 23:31:30 GMT.
With II_TIMEZONE_NAME = AUSTRALIA_BRISBANE, the query
select date(gmt_timestamp (1234567890))
returns 14-feb-2009 09:31:30
INTERVAL
interval (unit,date_interval)
Result Type: 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.
select(interval('days', '5 years'))
returns 1826.213
This function is not supported for the Oracle and MS SQL Enterprise Access products.
_DATE
_date(s)
Result Type: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.
select _date(123456)
returns 2-jan-70
_DATE4
_date4(s)
Result Type: 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.
With II_DATE_FORMAT set to US, the query:
select _date4(123456)
returns 02-jan-1970
while with II_DATE_FORMAT set to MULTINATIONAL, the query:
select _date4(123456)
returns 02/01/1970
_TIME
_time(s)
Result Type: 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).
select _time(123456)
returns 02:17