Skip to main content
Skip table of contents

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

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 CData 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 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 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'.

(info) Compatibility issue: TIMESTAMPDIFF typically returns an integer, however, CData 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

DATE_TRUNC function available since v4.0.8

Parsing Date Data Types from Strings

The CData 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)
GEra designatorTextG
AD
yYearYear 

yyyy

1996
yy
96
YWeek year (the year to which the given week belongs)
Year

YYYY

2009
YY
09
MMonth in yearMonthMMMM
July
MMMJul
MM07
wWeek in year (number of the given week in the given year)
Number w27
WWeek in monthNumber W2
DDay in year Number DDD003; 070; 189
dDay in monthNumber dd10
FDay of the week in a monthNumber F2
EDay name in weekText EEEETuesday
EEETue
uDay number of week (1 = Monday, ..., 7 = Sunday)Number u1
aAm/pm markerText aPM
HHour in a day (0-23)Number H0
kHour in day (1-24)Number kk04
KHour in am/pm (0-11)Number K4
hHour in am/pm (1-12)Number hh01
mMinute in hour Number m30
sSecond in minuteNumber s55
SMillisecond Number SSS978
zTime zoneGeneral time zonezzzz
Pacific Standard Time
zPST
ZTime zoneRFC 822 time zoneZ-0800
XTime zoneISO 8601 time zoneX-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 CData 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 CData 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


JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.