Date and time functions return or operate on dates, times, or timestamps.

Parse and format date/time functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines formats in the Oracle Java documentation.

Function

Definition

Data Type Constraint

CURDATE()

Returns current date

returns date

CURTIME()

Returns current time

returns time

NOW()

Returns current timestamp (date and time)

returns timestamp

DAYNAME(x)

Returns name of the day

x in {date, timestamp}, returns string

DAYOFMONTH(x)

Returns day of the month

x in {date, timestamp}, returns integer

DAYOFWEEK(x)

Returns day of the week (Sunday=1)

x in {date, timestamp}, returns integer

DAYOFYEAR(x)

Returns Julian day number

x in {date, timestamp}, returns integer

EXTRACT(YEAR | MONTH | DAY | HOUR | MINUTE | SECOND FROM X)

Returns the given field value from the date value x. Produces the same result as the associated YEAR, MONTH, DAYOFMONTH, HOUR, MINUTE, SECOND functions. The SQL specification also allows for TIMEZONE_HOUR and TIMEZONE_MINUTE as extraction targets. In the Data Virtuality Server, all date values are in the time zone of the server

x in {date, time, timestamp}, returns integer

FORMATDATE(x, y)

Formats date x using format y

x is date, y is string, returns string

FORMATTIME(x, y)

Formats time x using format y

x is time, y is string, returns string

FORMATTIMESTAMP(x, y)

Formats timestamp x using format y

x is timestamp, y is string, returns string

FROM_UNIXTIME (unix_timestamp)

Returns the Unix timestamp (in seconds) as timestamp value

unix_timestamp (in seconds) where unix_timestamp in {byte, short, integer} and unix_timestamp <= 2147483647

HOUR(x)

Returns hour (in military 24-hour format)

x in {time, timestamp}, returns integer

MINUTE(x)

Returns minute

x in {time, timestamp}, returns integer

MODIFYTIMEZONE (timestamp, endTimeZone, startTimeZone)

Returns a timestamp based upon the incoming timestamp adjusted for the differential between the start and end time zones. i.e. if the server is in GMT-6, MODIFYTIMEZONE{ts '2006-01-10 04:00:00.0'}(,'GMT-7', 'GMT-8') will return the timestamp {ts '2006-01-10 05:00:00.0'} as read in GMT-6. The value has been adjusted 1 hour ahead to compensate for the difference between GMT-7 and GMT-8. If a time zone is not understood, no error will be thrown, but the function will implicitly interpret it as GMT

startTimeZone and endTimeZone are strings, returns a timestamp

MODIFYTIMEZONE (timestamp, startTimeZone)

Returns timestamp in the same manner as MODIFYTIMEZONE (timestampendTimeZonestartTimeZone), but will assume that the endTimeZone is the same as the server process

Timestamp is a timestamp; startTimeZone  is a string, returns a timestamp

MONTH(x)

Returns month

x in {date, timestamp}, returns integer

MONTHNAME(x)

Returns name of the month

x in {date, timestamp}, returns string

PARSEDATE(x, y)

Parses date from x using format y

x, y in {string}, returns date

PARSETIME(x, y)

Parses time from x using format y

x, y in {string}, returns time

PARSETIMESTAMP(x,y)

Parses timestamp from x using format y

x, y in {string}, returns timestamp

PARSETIMESTAMP(x,y,z)

Parses timestamp from x using format y and language tag z

SELECT PARSETIMESTAMP('Thu Dec 23 18:26:07 +0000 2010', 'EEE MMM dd HH:mm:ss ZZZZZ yyyy', 'en');;
SELECT PARSETIMESTAMP('Thu Dec 23 18:26:07 +0000 2010', 'EEE MMM dd HH:mm:ss ZZZZZ yyyy', 'en.US');;

For more information about valid language tags, please refer to the Oracle documentation.

x, y, z in {string}, returns timestamp

QUARTER(x)

Returns quarter

x in {date, timestamp}, returns integer

SECOND(x)

Returns seconds

x in {time, timestamp}, returns integer

SERVERTIMEZONE()Returns the server time zone according to the list of tz database time zonesreturns a string in tz tag format, e.g.  Africa/Abidjan
TIMESTAMPADD(interval, count, timestamp)

Adds a specified interval count to the timestamp. Interval can be one of the following keywords:

  1. SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second)
  2. SQL_TSI_SECOND - seconds
  3. SQL_TSI_MINUTE - minutes
  4. SQL_TSI_HOUR - hours
  5. SQL_TSI_DAY - days
  6. SQL_TSI_WEEK - weeks using Sunday as the first day
  7. SQL_TSI_MONTH - months
  8. SQL_TSI_QUARTER - quarters (3 months)
  9. SQL_TSI_YEAR - years

The full interval amount based upon calendar fields will be added. For example, adding 1 QUARTER will increase the timestamp by three full months and not just the start of the next calendar quarter.

Count can be of long or integer data type but in the range of integer data type ( -2,147,483,648 to 2,147,483,647 ) in both cases

count in {integer, long}, timestamp in {date, time, timestamp},  returns  timestamp 

TIMESTAMPCREATE(date, time)

Creates timestamp from date and time

date in {date}, time in {time}, returns timestamp

TIMESTAMPDIFF(interval, startTime, endTime)

Calculates the number of date part intervals crossed between the two timestamps.

Interval can be one of the same keywords as used by timestampadd; startTime, endTime are in {date, time, timestamp}, and the return value is long.

If (endTime > startTime), a non-negative number will be returned. If (endTime < startTime), a non-positive number will be returned. The date part difference is counted regardless of how close the timestamps are. For example, '2000-01-02 00:00:00.0' is still considered 1 hour ahead of '2000-01-01 23:59:59.999999'.

(info) Compatibility issue: Timestampdiff typically returns an integer; however, the Data Virtuality Server version returns a long. You may receive an exception if you expect a value out of the integer range from a pushed down timestampdiff.

startTime, endTime are in {date, time, timestamp}, returns long

WEEK(x)

Returns week in a year

x in {date, timestamp}, returns integer

YEAR(x)

Returns four-digit year

x in {date, timestamp}, returns integer

Parsing Date Data Types from Strings

The Data Virtuality Server does not implicitly convert strings that contain dates presented in different formats, such as ‘19970101’ and ‘31/1/1996’, to date-related data types. You can, however, use the parseDate, parseTime, and parseTimestamp functions below to explicitly convert strings with a different format to the appropriate data type. These functions use the convention established within the java.text.SimpleDateFormat class to define the formats you can use with these functions. You can learn more about how this class defines date and time string formats from the  Oracle Java documentation.

For example, you could use these function calls with the formatting string that adheres to the java.text.SimpleDateFormat convention, to parse strings and return the data type you need:

String

Function Call To Parse String

'1997010'

PARSEDATE(myDateString, 'yyyyMMdd')

'31/1/1996'

PARSEDATE(myDateString, 'dd''/''MM''/''yyyy')

'22:08:56 CST'

PARSETIME(myTime, 'HH:mm:ss z')

'03.24.2003 at 06:14:32'

PARSETIMESTAMP(myTimestamp, 'MM.dd.yyyy ''at'' hh:mm:ss')

Date and Time Pattern Strings

Date and time formats are specified by date and time pattern strings. Within date and time pattern strings, unquoted letters from 'A' to 'Z' and from 'a' to 'z' are interpreted as pattern letters representing the components of a date or time string. Text can be quoted using single quotes (') to avoid interpretation. "''" represents a single quote. All other characters are not interpreted; they are copied into the output string during formatting or matched against the input string during parsing.

The following pattern letters are defined (all other characters from 'A' to 'Z' and from 'a' to 'z' are reserved):

Letter

Date or Time Component

Presentation

Examples

GEra designatorTextAD
yYearYear 1996; 96
YWeek yearYear 2009; 09
MMonth in yearMonthJuly; Jul; 07
wWeek in yearNumber 27
WWeek in monthNumber 2
DDay in year Number 189
dDay in monthNumber 10
FDay of week in the monthNumber 2
EDay name in the weekText Tuesday; Tue
uDay number of week (1 = Monday, ..., 7 = Sunday)Number 1
aAm/pm markerText PM
HHour in day (0-23)Number 0
kHour in day (1-24)Number 24
KHour in am/pm (0-11)Number 0
hHour in am/pm (1-12)Number 12
mMinute in hour Number 30
sSecond in minuteNumber 55
SMillisecond Number 978
zTime zoneGeneral time zonePacific Standard Time; PST; GMT-08:00
ZTime zoneRFC 822 time zone-0800
XTime zoneISO 8601 time zone-08; -0800; -08:00

Specifying Time Zones

Time zones can be specified in several formats. Common abbreviations such as EST for "Eastern Standard Time" are allowed, but we do not recommend using them, as they can be ambiguous. Unambiguous time zones are defined as continents or oceans/largest cities. For example, America/New_York, America/Buenos_Aires, or Europe/London. Additionally, you can specify a custom time zone by GMT offset: GMT[+/-]HH:MM (e.g., GMT-05:00).

Please note that the Data Virtuality Server has no control over time zones set at remote data sources. Therefore, if there is a difference in time zones between the data source and the Data Virtuality Server, there may be differences in the results of executing date-time functions whenever the time zone is involved.