4. Elements of QUEL Statements : Functions : Scalar : Date
 
Share this page                  
Date
QUEL supports functions that derive values from absolute dates and from interval dates. These functions operate on columns that contain date values. 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:
Unit
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
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, blanks are returned for the time portion of the result.
For example, the query retrieve (dcolumn=date_gmt("1-1-93 10:13 PM PST")) returns the following value:
1998_01_01 06:13:00 GMT
gmt_timestamp(s)
Any character data type
Converts s (where s is an integer that represents the number of seconds since January 1, 1970 GMT) into the GMT character equivalent with the format yyyy_mm_dd hh:mm:ss GMT.
For example, the query
retrieve (dcolumn = gmt_timestamp(123456)) returns the following value:
1970_01_02 10:17:36 GMT
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 retrieve (icolumn = interval("days", "5 years")) returns the following value:
1826.213
_date(s)
Any character data type
Returns a 9-character string giving the date s seconds after January 1, 1970 GMT. The output format is "dd-mmm-yy".
For example, the query retrieve (dcolumn = _date(123456)) returns the following value:
2-jan-1970
_time(s)
Any character data type
Returns a 5-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 retrieve (tcolumn = _time(123456)) returns the following value:
02:17