Date and Time Functions

CURDATE()

Returns current date

returns date

CURTIME()

Returns current time

returns time

NOW()

Returns current timestamp (date and time)

returns timestamp

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

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

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) 

QUARTER(x)

Returns quarter

x in {date, timestamp}, returns integer

SECOND(x)

Returns seconds

x in {time, timestamp}, returns integer

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

 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

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

'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')