Date and Time Functions

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.

To view the full table, click the expand button in its top right corner

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

DATE_TRUNC('precision',x)

Truncates DATE, TIME, or TIMESTAMP to a specified precision (for example, DATE_TRUNC('MONTH', startDate). Valid values for precision are:

MICROSECONDS
MILLISECONDS
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
DECADE
CENTURY
MILLENNIUM

x in {time, date, timestamp}, 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

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, then 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 then 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 (timestamp, endTimeZone, startTimeZone), 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');;

More information about valid language tags: https://docs.oracle.com/javase/7/docs/api/java/util/Locale.html#forLanguageTag(java.lang.String)

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 zones returns 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:

SQL_TSI_FRAC_SECOND - fractional seconds (billionths of a second)
SQL_TSI_SECOND - seconds
SQL_TSI_MINUTE - minutes
SQL_TSI_HOUR - hours
SQL_TSI_DAY - days
SQL_TSI_WEEK - weeks using Sunday as the first day
SQL_TSI_MONTH - months
SQL_TSI_QUARTER - quarters (3 months)
SQL_TSI_YEAR - years

The full interval amount based upon calendar fields will be added. For example, adding 1 QUARTER will move the timestamp up by three full months and not just to 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 a 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'.

Compatibility issue: TIMESTAMPDIFF typically returns an integer, however, 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're simply 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; the patterns listed in the table are examples, not an exhaustive list of patterns that can be used):

Letter	Date or Time Component	Presentation	Example Pattern(s)	Example(s)
G	Era designator	Text	G	AD
y	Year	Year	yyyy	1996
y	Year	Year	yy	96
Y	Week year (the year to which the given week belongs)	Year	YYYY	2009
Y	Week year (the year to which the given week belongs)	Year	YY	09
M	Month in year	Month	MMMM	July
			MMM	Jul
			MM	07
w	Week in year (number of the given week in the given year)	Number	w	27
W	Week in month	Number	W	2
D	Day in year	Number	DDD	003; 070; 189
d	Day in month	Number	dd	10
F	Day of the week in a month	Number	F	2
E	Day name in week	Text	EEEE	Tuesday
E	Day name in week	Text	EEE	Tue
u	Day number of week (1 = Monday, ..., 7 = Sunday)	Number	u	1
a	Am/pm marker	Text	a	PM
H	Hour in a day (0-23)	Number	H	0
k	Hour in day (1-24)	Number	kk	04
K	Hour in am/pm (0-11)	Number	K	4
h	Hour in am/pm (1-12)	Number	hh	01
m	Minute in hour	Number	m	30
s	Second in minute	Number	s	55
S	Millisecond	Number	SSS	978
z	Time zone	General time zone	zzzz	Pacific Standard Time
z	Time zone	General time zone	z	PST
Z	Time zone	RFC 822 time zone	Z	-0800
X	Time zone	ISO 8601 time zone	X	-08
			XXX	-0800
			XXXX	-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 in the form of continent or ocean/largest city. 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.

Parsing Date Data Types from Strings

Date and Time Pattern Strings

Specifying Time Zones

See Also