Date and Time Functions
You are looking at an older version of the documentation. The latest version is found here.
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 | |
|---|---|---|---|
| Returns current date | returns date | |
| Returns current time | returns time | |
| Returns current timestamp (date and time) | returns timestamp | |
DATE_TRUNC(precision,x) | Truncates
| x in {time, date, timestamp}, returns timestamp | |
| Returns name of the day | x in {date, timestamp}, returns string | |
| Returns day of the month | x in {date, timestamp}, returns integer | |
| Returns day of the week (Sunday=1) | x in {date, timestamp}, returns integer | |
| 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 | |
| Formats date x using format y | x is date, y is string, returns string | |
| Formats time x using format y | x is time, y is string, returns string | |
| Formats timestamp x using format y | x is timestamp, y is string, returns string | |
| Returns the Unix timestamp (in seconds) as Timestamp value | unix_timestamp (in seconds) where unix_timestamp in {byte, short, integer} and unix_timestamp <= 2147483647 | |
| Returns hour (in military 24-hour format) | x in {time, timestamp}, returns integer | |
| Returns minute | x in {time, timestamp}, returns integer | |
| 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 | |
| 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 | |
| Returns month | x in {date, timestamp}, returns integer | |
| Returns name of the month | x in {date, timestamp}, returns string | |
| Parses date from x using format y | x, y in {string}, returns date | |
| Parses time from x using format y | x, y in {string}, returns time | |
| 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
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 | |
| Returns quarter | x in {date, timestamp}, returns integer | |
| 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:
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 | |
| Creates timestamp from date and time | date in {date}, time in {time}, returns timestamp | |
| 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 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'.
| startTime, endTime are in {date, time, timestamp}, returns long | |
| Returns week in a year | x in {date, timestamp}, returns integer | |
| Returns four-digit year | x in {date, timestamp}, returns integer |
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.DATE_TRUNC function available since v3.13
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 |
|---|---|
|
|
|
|
|
|
|
|
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 |
| yy | 96 | |||
| Y | Week year (the year to which the given week belongs) | Year | YYYY | 2009 |
| 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 |
| 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 | 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.
See Also
Setting the Server Locale for the WEEK and DAYOFWEEK Functions for information on how to check if the WEEK and DAYOFWEEK functions treat Sunday as the first or last of week and how to change it
Create Unix Timestamps to learn how to create a Unix timestamp for the current/any given timestamp
Use Values Larger Than 2147483647 with FROM_UNIXTIME for a workaround in case you need to use a value beyond 32-bit signed binary integer
Filtering for Dates that Belong to the Current Week with SQL for using TIMESTAMPADD(), DAYOFWEEK() and CURDATE() in order to dynamically find the first and the last day of the current week
MSSQL ENDOFMONTH Replacement for a workaround if you are using Microsoft SQL
Check for (!)= '' and (NOT) NULL at the Same Time for a really short guide which may still come in handy