ADDDAY¶

Description

The ADDDAY function returns the date passed as parameter with its field day rolled up (or down, if the increment is negative) by the amount specified.

Syntax

ADDDAY( <date:date>, <increment:int> ):date

ADDDAY(<date:date>, <increment:long> ):date

• date. Required. The date field.

• increment. Required. The amount to increase the field day. If the number is negative, the field is decreased.

Example

SELECT time, ADDDAY(time, 8)
FROM v

ADDHOUR¶

Description

The ADDHOUR function returns the date passed as parameter with its field hour rolled up (or down, if the increment is negative) by the amount specified.

Syntax

ADDHOUR( <date:date, increment:int> ):date

ADDHOUR( <date:date, increment:long> ):date

• date. Required. The date field.

• increment. Required. The amount to increase the field hour. If the number is negative, the field is decreased.

Example

SELECT time, ADDHOUR(time, -2)
FROM v

ADDMINUTE¶

Description

The ADDMINUTE function returns the date passed as parameter with its field minute rolled up (or down, if the increment is negative) by the amount specified.

Syntax

ADDMINUTE( <date:date, increment:int> ):date

ADDMINUTE( <date:date, increment:long> ):date

• date. Required. The date field.

• increment. Required. The amount to increase the field minute. If the number is negative, the field is decreased.

Example

SELECT time, ADDMINUTE(time, 10)
FROM v

ADDMONTH¶

Description

The ADDMONTH function returns the date passed as parameter with its field month rolled up (or down, if the increment is negative) by the amount specified.

Syntax

ADDMONTH( <date:date, increment:int> ):date

ADDMONTH( <date:date, increment:long> ):date

• date. Required. The date field.

• increment. Required. The amount to increase the field month. If the number is negative, the field is decreased.

Example

SELECT time, ADDMONTH(time, -12)
FROM v

ADDSECOND¶

Description

The ADDSECOND function returns the date passed as parameter with its field second rolled up (or down, if the increment is negative) by the amount specified.

Syntax

ADDSECOND( <date:date, increment:int> ):date

ADDSECOND( <date:date, increment:long> ):date

• date. Required. The date field.

• increment. Required. The amount to increase the field second. If the number is negative, the field is decreased.

Example

SELECT time, ADDSECOND(time, 5)
FROM v

ADDWEEK¶

Description

The ADDWEEK function returns the date passed as parameter with its field week rolled up (or down, if the increment is negative) by the amount specified. That is, rolled up or down in multiples of 7 days.

Syntax

ADDWEEK( <date:date, increment:int> ):date

ADDWEEK( <date:date, increment:long> ):date

• date. Required. The date field.

• increment. Required. Number of times to increase the field day, 7 days. If the number is negative, the field is decreased. If 0, it returns date, unmodified.

Example

SELECT time, ADDWEEK(time, -2)
FROM v

We can see that the date is rolled down 2 weeks. It rolls down, instead of rolling up, because the parameter increment is a negative number.

ADDYEAR¶

Description

The ADDYEAR function returns the date passed as parameter with its field year rolled up (or down, if the increment is negative) by the amount specified.

Syntax

ADDYEAR( <date:date, increment:int> ):date

ADDYEAR( <date:date, increment:long> ):date

• date. Required. The date field.

• increment. Required. The amount to increase the field year. If the number is negative, the field is decreased.

Example

SELECT time, ADDYEAR(time, 7)
FROM v

CURRENT_DATE¶

Description

The CURRENT_DATE function returns a date value that represents the current date, with the fields hour, minute, second and millisecond set to 0.

If you want to obtain a date value that represents the current date and time, use the function NOW() (see section NOW)

Syntax

CURRENT_DATE() : date

CURRENT_DATE : date

You can invoke this function with or without brackets. See the following example.

Example

SELECT CURRENT_DATE() AS current_date_1, CURRENT_DATE AS
current_date_2

EXTRACT¶

Description

The EXTRACT function extracts the year, month, day, hour, minute or second from a date value.

Syntax

EXTRACT ( <part of field> FROM <expression:date> )

EXTRACT ( <part of field> FROM <field:date> )

• part of field. Required. It can be one of the following values:

  • YEAR: returns the year of the date

  • MONTH: returns the month of the date

  • DAY: returns the day of the date

  • HOUR: returns the hour of the date

  • MINUTE: returns the minute of the date

  • SECOND: returns the second of the date

  • MILLISECOND: returns the millisecond of the date

  • QUARTER: returns the quarter of the date. The first quarter is 1, the last one is 4.

  • WEEK: returns the week number in the year. The first week of the year is 1.

  • DOW: returns the day of the week, between Sunday (0) and Saturday (6)

  • DOY: returns the day of the year. The first day of the year is 1.

• date or field. Required. A field of type date or an expression that returns a date value.

Examples

Example 1

SELECT time, EXTRACT(YEAR FROM time) AS year
FROM view

This query extracts the year from the column of a field of the result.

Example 2

SELECT time, EXTRACT(ADDDAY(time, 1)) AS next_day
FROM view

This query extracts the hour of a date value returned by an expression.

FIRSTDAYOFMONTH¶

Description

The FIRSTDAYOFMONTH function returns the date passed as parameter, with the field day rolled down to the first day of the month. If the date passed as parameter already is the first day of the month, it returns the parameter unchanged.

Syntax

FIRSTDAYOFMONTH( <date:date> ):date

• date. Required.

Example

SELECT time, FIRSTDAYOFMONTH(time) FROM v

FIRSTDAYOFWEEK¶

Description

The FIRSTDAYOFWEEK function returns the date passed as parameter, with the field day rolled down to the first day of the week.

If the date passed as parameter already is the first day of the week, it returns the parameter unchanged.

The first day of the week depends on the locale of the view and the query.

For example, in the locale us_pst (U.S. Pacific Standard Time zone), the first day of the week is Sunday, but in es_euro (Spain’s time zone), the first day of the week is Monday.

If the function is delegated to a database, the result may depend on the underlying database.

You can see the i18n of a view in the Advanced dialog of the view. See more about this in the section Internationalization Configuration of the Administration Guide.

Syntax

FIRSTDAYOFWEEK( <date:date> ):date

• date. Required.

Example

SELECT time, FIRSTDAYOFWEEK(time)
FROM v

We can see that in the second row the day already is the first day of the week, so the output of the function is the same as the input.

FORMATDATE¶

Description

The FORMATDATE function returns a string containing a date-type formatted using the given pattern.

This function relies on the date and time formatting system of Java. The Java Date and time patterns used in Virtual DataPort lists the date and time patterns of Java.

E.g. the function FORMATDATE is delegated to MySQL as the function DATE_FORMAT. If this function does not support the pattern indicated in the parameter date_pattern, the query will fail.

To solve this problem, you can do the following:

• Use a different pattern

• Or, avoid delegating the function FORMATDATE to the database. To do this, remove the function FORMATDATE from the “Delegate Scalar Functions List” of the “Source configuration” of the data source. The section Data Source Configuration Properties of the Administration Guide explains how to do this.

Syntax

FORMATDATE( <date pattern:text>, <date:date>, [ <i18n:text> ] ):text

• date pattern. Required. Pattern used to format the date passed in the second parameter (see section Date and Time Pattern Strings for more information about date patterns format).

• date. Required. The date value to be formatted.

• i18n. Optional. Internationalization configuration. When date_pattern contains the pattern of the day in the week (EEE or EEEE) or the name of the month (MMM or MMMM), this parameter indicates the language used to return these two elements.

  The value of this parameter has to be one of the i18n maps of the Server. E.g. us_pst, us_est, gb, de, etc.

Examples

Example 1

SELECT date, FORMATDATE('yyyy.MM.dd G ''at'' HH:mm:ss', date) AS
format_date
FROM v

Text between single quotes is not interpreted (see 'at') and is copied to the output as it is.

SELECT formatdate('yyyy.MM.dd G ''at'' HH:mm:ss', now())

Example 2

SELECT date, formatdate('h:mm a', date) AS format_date
FROM v

Example 3

SELECT date, formatdate('yyMMddHHmmss', date) AS format_date
FROM v

Example 4

SELECT date, FORMATDATE('MMMM, EEEE dd, yyyy', date, 'us_pst') AS
    format_date
FROM v

Example 5

SELECT date, FORMATDATE('MMMM, EEEE dd, yyyy', date, 'de') AS
    format_date
FROM v

The only difference between examples 4 and 5 is the parameter i18n. In example 4, the parameter is us_pst, so the function returns the names of the days in the week and months in English. In Example 5, as the i18n is de, the function returns these values in German.

GETDAY¶

Description

The GETDAY function returns the “day” field of a given date. The function returns a long data-type ranging from 1 to 31.

Syntax

GETDAY( <date:date> ):long

• date. Required. Date to retrieve the day from.

Example

SELECT date, getday(date) as day
FROM v;

GETDAYOFWEEK¶

Description

The GETDAYOFWEEK function returns the number of the day of the week of this date.

The first day of the week is 1 and the last day is 7.

The first day of the week depends on the locale of the view and the query. For example, in the locale us_pst (U.S. Pacific Standard Time zone), the first day of the week is Sunday, but in es_euro (Spain’s time zone), the first day of the week is Monday.

If the function is delegated to a database, the result may depend on the underlying database. E.g. Oracle 11g always considers that the first day of the week is Monday.

You can see the i18n of a view in the Advanced dialog of the view. See more about this in the section Internationalization Configuration of the Administration Guide.

Syntax

GETDAYOFWEEK( <date:date> ):long

• date. Required.

Examples

Example 1

SELECT NOW(), GETDAYOFWEEK(NOW())
CONTEXT('i18n' = 'US_PST')

Example 2

SELECT NOW(), GETDAYOFWEEK(NOW())
CONTEXT('i18n' = 'ES_EURO')

The difference between Example 1 and Example 2 is the i18n of the query, set in the CONTEXT clause. When you do not add the i18n parameter to the CONTEXT, the query uses the i18n of the view.

GETDAYOFYEAR¶

Description

The GETDAYOFYEAR function returns the number of the day in the year of the date.

The first day of the year is 1.

Syntax

GETDAYOFYEAR( <date:date> ):long

• date. Required.

Example

SELECT TO_DATE('dd-MM-yyyy', '01-01-2013'),
GETDAYOFYEAR( TO_DATE('dd-MM-yyyy', '01-01-2013') )

GETDAYSBETWEEN¶

Description

The GETDAYSBETWEEN function returns the number of days between two dates.

It returns 0 if both dates represent the same day.

It returns a positive number, if the first parameter is first.

It returns a negative number if the second parameter is first.

Syntax

GETDAYSBETWEEN( <date1:date>, <date2:date> ):long

• date1. Required.

• date2. Required.

Example

SELECT date1, date2, GETDAYSBETWEEN(date1, date2)
FROM view

GETHOUR¶

Description

The GETHOUR function returns the “hour” field of a given date. The function returns a long data-type, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.).

Syntax

GETHOUR( <date:date> ):long

• date. Required. Date to retrieve the hour from.

Example

SELECT date, gethour(date) as hour
FROM v;

GETMILLISECOND¶

The GETMILLISECOND function returns the “milliseconds” field of a given date.

Syntax

GETMILLISECOND ( <date:date> ):long

• date. Required.

GETMINUTE¶

Description

The GETMINUTE function returns the “minute” field of a given date. The function returns a value of type long, ranging from 0 to 59.

Syntax

GETMINUTE( <date:date> ):long

• date. Required. Date to retrieve the minute from.

Example

SELECT date, getMinute(date) as minute
FROM v;

GETMONTH¶

Description

The GETMONTH function returns the number of month in a year of a given date. The function returns a long data-type, ranging from 1 (January) to 12 (December).

Syntax

GETMONTH( <date:date> ):long

• date. Required. Date to retrieve the number of month from.

Example

SELECT date, getMonth(date) as month
FROM v

GETMONTHSBETWEEN¶

Description

The GETMONTHSBETWEEN function returns the number of months between two dates.

It returns 0 if both dates represent the same month.

It returns a positive number, if the first parameter is first.

It returns a negative number if the second parameter is first.

Syntax

GETMONTHSBETWEEN ( <date1:date>, <date2:date> ):long

• date1. Required.

• date2. Required.

Example

SELECT date1, date2, GETMONTHSBETWEEN(date1, date2)

GETQUARTER¶

Description

The GETQUARTER function returns the quarter of the year of a given date.

The result ranges from 1 to 4. 1 is the first quarter of the year (January to March), 2 is the second (April to June), etc.

Syntax

GETQUARTER ( <date:date> ):long

• date. Required. Date from which you want to retrieve its quarter.

Example

SELECT date, GETQUARTER(date) as quarter
FROM v

GETSECOND¶

Description

The GETSECOND function returns the “second” field of a given date. The function returns a value of type long that ranges from 0 to 59.

Syntax

GETSECOND( <date:date> ):long

• date. Required. Date to retrieve the second from.

Example

SELECT date, GETSECOND(date) as second
FROM v

GETTIMEINMILLIS¶

Description

The GETTIMEINMILLIS function returns the number of milliseconds from January 1, 1970, 00:00:00 GMT to the date passed as parameter.

It returns a negative number if the date is prior to 1970.

Syntax

GETTIMEINMILLIS( <date:date> ):long

• date. Required.

Example

SELECT date, getTimeInMillis(date) as milliseconds
FROM v

GETWEEK¶

Description

The GETWEEK function returns the week of the year of a given date.

The first week of the year is 1. As defined in the standard ISO8601, the first week of the year is that in which at least 4 days are in the year. As a result of this definition, depending on the year the day 1 of the year may be considered to belong to the previous year.

Syntax

GETWEEK ( <date:date> ):long

• date. Required. Date from which you want to retrieve the week of the year.

Example

SELECT date, GETWEEK(date) as week
FROM v

GETYEAR¶

Description

The GETYEAR function returns the “year” field of a given date.

Syntax

GETYEAR( <date:date> ):long

• date. Required. Date to retrieve the year from.

Example

SELECT date, GETYEAR(date) as year
FROM Dual();

LASTDAYOFMONTH¶

Description

The LASTDAYOFMONTH function returns the date passed as parameter with the field day rolled up to the last day of the month. If the date passed as parameter already is the last day of the month, it returns the parameter unchanged.

Syntax

LASTDAYOFMONTH( <date:date> ):date

• date. Required.

Example

SELECT time, LASTDAYOFMONTH(time)
FROM v

We can see that in the first row the day is already the last day of the month, so the output of the function is the same as the input.

LASTDAYOFWEEK¶

Description

The LASTDAYOFWEEK function returns the date passed as parameter with the field day rolled up to the last day of the week.

If the date passed as parameter already is the last day of the week, it returns the parameter unchanged.

The last day of the week depends on the locale of the view and the query.

For example, in the locale us_pst (U.S. Pacific Standard Time zone), the last day of the week is Saturday, but in es_euro (Spain’s time zone), the last day of the week is Sunday.

If the function is delegated to a database, the result may depend on the underlying database.

You can see the i18n of a view in the Advanced dialog of the view. See more about this in the section Internationalization Configuration of the Administration Guide.

Syntax

LASTDAYOFWEEK( <date:date> ):date

• date. Required.

Example

SELECT time, LASTDAYOFWEEK(time)
FROM v

MAX¶

See appendix MAX.

MIN¶

See appendix MIN.

NEXTWEEKDAY¶

Description

The NEXTWEEKDAY function returns this date with its field day rolled up to the day of the week indicated by the parameter weekDay.

If the parameter date already represents the day weekDay, the function rolls up the date to the same day of next week.

The days of the week are: Sunday = 0, Monday = 1, Tuesday = 2 …

Syntax

NEXTWEEKDAY( <date:date>, <week day:int> ):date

• date. Required.

• week day. Required. The day of the week that the date will be rolled up to.

Example

SELECT time, NEXTWEEKDAY(time, 3)
FROM v

NOW¶

Description

The NOW function returns the current date and time.

If you want to obtain a date value that represents the current date, but not the time, use the function CURRENT_DATE() (see section CURRENT_DATE)

Syntax

NOW():date

Example

SELECT now() as date_and_time_now
FROM Dual();

PREVIOUSWEEKDAY¶

Description

The PREVIOUSWEEKDAY function returns this date with its field day rolled down to the day of the week indicated by the parameter weekDay.

If the parameter date already represents the day weekDay, the function rolls down the date to the same day of previous week.

The days of the week are: Sunday = 0, Monday = 1, Tuesday = 2 …

Syntax

PREVIOUSWEEKDAY( <date:date>, <week day:int> ):date

• date. Required.

• week day. Required. The day of the week that the date will be rolled down to.

Example

SELECT time, previousweekday(time, 2)
FROM v

SUBTRACT¶

See SUBTRACT.

TO_DATE¶

Description

The TO_DATE function converts a text value containing a date in a specific format, into a value of type date that represents this date.

Syntax

TO_DATE( <date pattern:text>, <value:text> [, <i18n:text> ] [, <timestamp:boolean> ] ):date

• date pattern. Required. Pattern describing the date and time format of value, following the syntax defined by Java in the class java.text.SimpleDataFormat (see section Date and Time Pattern Strings for more information).

• value. Required. String that contains a date following the pattern of the parameter datePattern.

• i18n. Optional. Internationalization configuration. When date pattern contains the pattern of the day in the week (EEE or EEEE) or the name of the month (MMM or MMMM), this parameter indicates the language that the function expects these values to be in. For example, if value will contain the names of the months in German, the value of this parameter has to be de, unless the i18n of the database is de as well.

  The value of this parameter has to be one of the i18n maps of the Server. E.g. us_pst, us_est, gb, de, etc.

• timestamp. Optional. If false, the function sets to 0 the fields that represent the time: hour, minute, second and millisecond. Passing true to this parameter is the same as not passing it.

Examples

Example 1

SELECT TO_DATE('M dd yyyy HH:mm:ss', '3 05 2010 21:17:05')
FROM Dual();

Example 2

SELECT TO_DATE ('yyyyMMddHHmmss', '20100701102030')
FROM Dual();

Example 3

SELECT TO_DATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2001-07-04T12:08:56.235')
FROM Dual();

Example 4

SELECT TO_DATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2001-07-04T12:08:56.235', false)
FROM Dual();

Note about examples 3 and 4: as defined by the Java class java.text.SimpleDataFormat, the parts of the date pattern that are literals have to be surrounded with single quotes. In these two examples, T. But the single quote is a special character in a Virtual DataPort literal and it has to be escaped with another single quote. That is why the date pattern of the examples 3 and 4 contains ''T''.

Example 5

SELECT date_string, TO_DATE('MMMM, EEEE dd, yyyy', date_string, 'de')
FROM v

In this example, as the parameter i18n is de, the function expects the names of the month (MMM) and names of days in the week (EEEE) of date_string to be in German.

TRUNC¶

Description

The TRUNC function returns the date passed as parameter, truncated to a specific unit of measure.

This function has the same syntax as the function TRUNC(date) of the Oracle database. The parameter pattern also has the same syntax.

Syntax

TRUNC( <date:date> [, <pattern:text> ] ):date

• date. Required. Date to be truncated.

• pattern. The date is truncated to the unit specified by this parameter. If pattern is missing, date is truncated to the nearest day. The table below lists the possible values of this parameter.

Remarks

If a query uses this function and the execution engine delegates the function to a JDBC data source, the query may fail depending on the pattern and the database:

For example, a query with the function TRUNC(time, 'CC') will immediately fail if the function is delegated to SQL Server, Redshift or Snowflake, but it will work if it is executed by Denodo or delegated to any other database.

Examples

Example 1

SELECT time, TRUNC(time)
FROM v

As the parameter pattern is not present, the date is truncated to the day.

Example 2

SELECT time, TRUNC(time, 'MONTH')
FROM v

Example 3

SELECT time, TRUNC(time, 'Q')
FROM v

The pattern Q means that the date will be truncated to the quarter.