Datetime Functions¶
Datetime functions manipulate values of type intervaldaysecond
, intervalyearmonth
, localdate
, time
, timestamp
, timestamptz
and date
(deprecated).
The Execution Engine evaluates the functions that return the current datetime once per query. These functions are CURRENT_DATE
, LOCALTIMESTAMP
, CURRENT_TIMESTAMP
and LOCALTIME
. Note that if a query uses one of these functions twice and one of the occurrences is delegated to a database and the other, evaluated in Denodo, the result of the function may not be exactly the same.
The datetime functions are:
ADDDAY¶
Description
The ADDDAY
function returns the datetime passed as parameter with its
field day rolled up (or down, if the increment is negative) by the
amount specified.
Syntax
ADDDAY( <value:intervaldaysecond>, <increment> ):intervaldaysecond
ADDDAY( <value:localdate>, <increment> ):localdate
ADDDAY( <value:timestamp>, <increment> ):timestamp
ADDDAY( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDDAY( <value:date>, <increment> ):date
value
. Required. Datetime expression.increment
. Required. Amount to increase the field day. If the number is negative, the field is decreased. The type of the expression can be int or long.
Example
SELECT time, ADDDAY(time, 8)
FROM v
time |
addday |
---|---|
2020-06-29 19:19:41 |
2020-07-07 19:19:41 |
2020-12-31 22:59:56 |
2021-01-08 22:59:56 |
ADDHOUR¶
Description
The ADDHOUR
function returns the datetime passed as parameter with its
field hour rolled up (or down, if the increment is negative) by the
amount specified.
Syntax
ADDHOUR( <value:intervaldaysecond>, <increment> ):intervaldaysecond
ADDHOUR( <value:time>, <increment> ):time
ADDHOUR( <value:timestamp>, <increment> ):timestamp
ADDHOUR( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDDAY( <value:date>, <increment> ):date
value
. Required. Datetime expression.increment
. Required. The amount to increase the field hour. If the number is negative, the field is decreased. The type of the expression can be int or long.
Example
SELECT time, ADDHOUR(time, -2)
FROM v
time |
addhour |
---|---|
2019-06-29 19:19:41 |
2019-06-29 17:19:41 |
2020-06-30 01:00:00 |
2020-06-29 23:00:00 |
ADDMILLIS¶
Description
The ADDMILLIS
function adds milliseconds to a datetime value. The increment
could be greater or less than 0.
Syntax
ADDMILLIS( <value:intervaldaysecond> [, <increment>] ):intervaldaysecond
ADDMILLIS( <value:time> [, <increment>] ):time
ADDMILLIS( <value:timestamp> [, <increment>] ):timestamp
ADDMILLIS( <value:timestamptz> [, <increment>] ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDMILLIS( <value:date> [, <increment>] ):date
value
. Required. The datetime expression.increment
. Optional. If the value of the increment is not indicated, it will be 1 millisecond.
Examples
Example 1
SELECT timestamp, ADDMILLIS(timestamp)
FROM v;
timestamp |
addmillis |
---|---|
2019-01-29 01:01:02.345 |
2019-01-29 01:01:02.346 |
2000-01-01 00:00:01 |
2000-01-01 00:00:01.001 |
Example 2
SELECT timestamp, ADDMILLIS(timestamp, 3)
FROM v;
timestamp |
addmillis |
---|---|
2019-01-29 01:01:02.345 |
2019-01-29 01:01:02.348 |
2000-01-01 00:00:01 |
2000-01-01 00:00:01.003 |
ADDMINUTE¶
Description
The ADDMINUTE
function returns the datetime passed as parameter with its
field minute rolled up (or down, if the increment is negative) by the
amount specified.
Syntax
ADDMINUTE( <value:intervaldaysecond>, <increment> ):intervaldaysecond
ADDMINUTE( <value:time>, <increment> ):time
ADDMINUTE( <value:timestamp>, <increment> ):timestamp
ADDMINUTE( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDMINUTE( <value:date>, <increment> ):date
value
. Required. Datetime expression.increment
. Required. The amount to increase the field minute. If the number is negative, the field is decreased. The type of the expression can be int or long.
Example
SELECT time, ADDMINUTE(time, 10)
FROM v
Time |
addminute |
---|---|
2024-11-12 19:19:41 |
2024-11-12 19:29:41 |
2019-06-30 23:59:00 |
2019-07-01 00:09:00 |
ADDMONTH¶
Description
The ADDMONTH
function returns the datetime passed as parameter with its
field month rolled up (or down, if the increment is negative) by the
amount specified.
Syntax
ADDMONTH( <value:intervalyearmonth>, <increment> ):intervalyearmonth
ADDMONTH( <value:localdate>, <increment> ):localdate
ADDMONTH( <value:timestamp>, <increment> ):timestamp
ADDMONTH( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDMONTH( <value:date, increment:int> ):date
value
. Required. Datetime expression.increment
. Required. The amount to increase the field month. If the number is negative, the field is decreased. The type of the expression can be int or long.
Example
SELECT time, ADDMONTH(time, -12)
FROM v
time |
addmonth |
---|---|
2020-06-29 19:19:41 |
2019-06-29 19:19:41 |
2021-01-08 22:59:56 |
2020-01-08 22:59:56 |
ADDSECOND¶
Description
The ADDSECOND
function returns the datetime passed as parameter with its
field second rolled up (or down, if the increment is negative) by the
amount specified.
Syntax
ADDSECOND( <value:intervaldaysecond>, <increment> ):intervaldaysecond
ADDSECOND( <value:time>, <increment> ):time
ADDSECOND( <value:timestamp>, <increment> ):timestamp
ADDSECOND( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDSECOND( <value:date>, <increment> ):date
value
. Required. The datetime expression.increment
. Required. The amount to increase the field second. If the number is negative, the field is decreased. The type of the expression can be int or long.
Example
SELECT time, ADDSECOND(time, 5)
FROM v
time |
addsecond |
---|---|
2019-01-29 19:19:41 |
2019-01-29 19:19:46 |
2020-06-30 22:59:56 |
2020-06-30 23:00:01 |
ADDWEEK¶
Description
The ADDWEEK
function returns the datetime 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( <value:intervaldaysecond>, <increment> ):intervaldaysecond
ADDWEEK( <value:localdate>, <increment> ):localdate
ADDWEEK( <value:timestamp>, <increment> ):timestamp
ADDWEEK( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDWEEK( <value:date>, <increment> ):date
value
. Required. The datetime field.increment
. Required. Number of times to increase the field day, 7 days. If the number is negative, the field is decreased. If0
, it returnsvalue
, unmodified. The type of the expression can be int or long.
Example
SELECT time, ADDWEEK(time, -2)
FROM v
time |
addweek |
---|---|
2019-06-29 19:19:41 |
2019-06-15 19:19:41 |
2021-01-08 22:59:56 |
2020-12-25 22:59:56 |
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 datetime passed as parameter with its
field year rolled up (or down, if the increment is negative) by the
amount specified.
Syntax
ADDWEEK( <value:intervalyearmonth>, <increment> ):intervalyearmonth
ADDWEEK( <value:localdate>, <increment> ):localdate
ADDWEEK( <value:timestamp>, <increment> ):timestamp
ADDWEEK( <value:timestamptz>, <increment> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
ADDWEEK( <value:date>, <increment> ):date
value
. Required. The datetime field.increment
. Required. The amount to increase the field year. If the number is negative, the field is decreased. The type of the expression can be int or long.
Example
SELECT time, ADDYEAR(time, 7)
FROM v
time |
addyear |
---|---|
2020-01-29 19:19:41 |
2027-01-29 19:19:41 |
2021-07-28 22:59:56 |
2028-07-28 22:59:56 |
AT TIME ZONE¶
Description
The AT TIME ZONE
function returns the timestamp passed as parameter with its time zone changed to the time zone specified.
It’s equivalent to CONVERT_TIMEZONE
function.
Syntax
<timestamp:timestamp> AT TIME ZONE <targetTimezone:text> : timestamptz
<timestamptz:timestamptz> AT TIME ZONE <targetTimezone:text> : timestamp
targetTimezone
. Required. The timezone to convert the input timestamp to.timestamp
. Required. The timestamp to be converted.
Notes
<timestamp> AT TIME ZONE <targetTimezone>
is equivalent toCONVERT_TIMEZONE( targetTimezone:text, timestamp:timestamp ) : timestamptz
. It takes a timestamp without time zone, assigns the timezone specified converting it to a timestamptz and returns a TIMESTAMPTZ value.<timestamptz> AT TIME ZONE <targetTimezone>
is equivalent toCONVERT_TIMEZONE( targetTimezone:text, timestamptz:timestamptz ) : timestamp
. It takes a value of type TIMESTAMPTZ, moves it to the specified timezone and returns the date and time as a value of type TIMESTAMP (without time zone).
Example
SELECT now() AT TIME ZONE 'UTC';
now() |
convert_timezone |
---|---|
2022-12-29 19:00:00 |
2027-01-29 18:00:00 |
This result is using as target timezone the UTC timezone, which is 1 hour less than the local timezone. Local timezone used is CET (Central European Time).
CONVERT_TIMEZONE¶
Description
The CONVERT_TIMEZONE
function converts a timestamp from one timezone to another.
Syntax
CONVERT_TIMEZONE( sourceTimeZone:text, targetTimeZone:text, timestamp:timestamp ) : timestamp
CONVERT_TIMEZONE( targetTimezone:text, timestamp:timestamp ) : timestamptz
CONVERT_TIMEZONE( targetTimezone:text, timestamptz:timestamptz ) : timestamp
sourceTimeZone
. Required for the first syntax. The timezone of the input timestamp.targetTimeZone
. Required. The timezone to convert the input timestamp to.timestamp
. Required. The timestamp to be converted.
Example
SELECT CONVERT_TIMEZONE('CET', 'America/Los_Angeles', cast('timestamp','2022-01-19 08:00:00'))
convert_timezone |
---|
2022-01-18 23:00:00 |
SELECT CONVERT_TIMEZONE('America/Los_Angeles', cast('timestamp','2022-01-18 23:00:00'))
convert_timezone |
---|
2022-01-19 08:00:00+01:00 |
SELECT CONVERT_TIMEZONE('America/Los_Angeles', cast('timestamptz','2022-01-19 08:00:00'))
convert_timezone |
---|
2022-01-18 23:00:00 |
We can see that the timestamp ‘2022-01-19 08:00:00’ is being converted from CET (GMT+1) to ‘America/Los_Angeles’ and viceversa using the three different syntaxes.
CURRENT_DATE¶
Description
The CURRENT_DATE
function returns a localdate
value that represents
the current date.
Syntax
CURRENT_DATE() : localdate
CURRENT_DATE : localdate
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
current_date_1 |
current_date_2 |
---|---|
2024-11-12 |
2024-11-12 |
CURRENT_TIMESTAMP¶
Description
The CURRENT_TIMESTAMP
function returns the current “timestamp with time zone” (a value of type timestamptz
).
This function is invoked without parenthesis. That is, use CURRENT_TIMESTAMP
, not CURRENT_TIMESTAMP()
.
To obtain the current instant of time on a different type (e.g. localdate
, time
…) see the table Function to obtain the current datetime. If you do not need the time zone component, we recommend using LOCALTIMESTAMP instead because it is delegable to more databases than CURRENT_TIMESTAMP
; some databases do not support the type timestamp with time zone so Virtual DataPort cannot delegate the function CURRENT_TIMESTAMP
to these databases.
Syntax
CURRENT_TIMESTAMP:timestamptz
Example
SELECT CURRENT_TIMESTAMP as date_and_time_now
FROM dual();
date_and_time_now |
---|
Nov 12, 2024 10:03:12.186-05:00 |
EXTRACT¶
Description
The EXTRACT
function extracts the year, month, day, hour, minute or
second from a datetime
value.
Syntax
EXTRACT ( <part of field> FROM <value> )
part of field
. Required. It can be one of the following values:YEAR
: returns the year of the dateMONTH
: returns the month of the dateDAY
: returns the day of the dateHOUR
: returns the hour of the dateMINUTE
: returns the minute of the dateSECOND
: returns the second of the dateMILLISECOND
: returns the millisecond of the dateQUARTER
: 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.
value
Required. A datetime expression.
Examples
Example 1
SELECT time, EXTRACT(YEAR FROM time) AS year
FROM view
time |
year |
---|---|
2021-06-25 19:19:41 |
2021 |
2019-01-31 22:59:56 |
2019 |
This query extracts the year from the column of a field of the result.
Example 2
SELECT sale_date AS sale_date, EXTRACT(ADDDAY(CURRENT_DATE, 1)) AS next_day
FROM view
sale_date |
next_day |
---|---|
2020-06-30 |
1 |
2021-01-01 |
2 |
This query extracts the hour of a datetime
value returned by an
expression.
FIRSTDAYOFMONTH¶
Description
The FIRSTDAYOFMONTH
function returns the datetime passed as parameter,
with the field day rolled down to the first day of the month. If the
datetime passed as parameter already is the first day of the month, it
returns the parameter unchanged.
Syntax
FIRSTDAYOFMONTH( <value:localdate> ):localdate
FIRSTDAYOFMONTH( <value:timestamp> ):timestamp
FIRSTDAYOFMONTH( <value:timestamptz> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
FIRSTDAYOFMONTH( <value:date> ):date
value
. Required.
Example
SELECT sale_date, FIRSTDAYOFMONTH(sale_date) FROM v
sale_date |
firstdayofmonth |
---|---|
2019-06-29 19:19:41 |
2019-06-01 19:19:41 |
2021-01-08 22:59:56 |
2021-01-01 22:59:56 |
2021-01-01 22:59:56 |
2021-01-01 22:59:56 |
FIRSTDAYOFWEEK¶
Description
The FIRSTDAYOFWEEK
function returns the datetime passed as parameter,
with the field day rolled down to the first day of the week.
If the datetime 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( <value:localdate> ):localdate
FIRSTDAYOFWEEK( <value:timestamp> ):timestamp
FIRSTDAYOFWEEK( <value:timestamptz> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
FIRSTDAYOFWEEK( <value:date> ):date
value
. Required.
Example
SELECT time, FIRSTDAYOFWEEK(time)
FROM v
time |
firstdayofweek |
---|---|
Wednesday, Jun 29, 2005 19:19:41 |
Monday, Jun 27, 2005 19:19:41 |
Monday, Jan 10, 2011 22:59:56 |
Monday, Jan 10, 2011 22:59:56 |
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 datetime-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.
Syntax
FORMATDATE( <datetime pattern:text>, <datetime>, [ <i18n:text> ] ):text
datetime pattern
. Required. Pattern used to format the datetime passed in the second parameter (see section Date and Time Pattern Strings for more information about date patterns format).In order to delegate this function to a database, the execution engine translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine will execute the function instead of delegating it to the database.
datetime
. Required. The datetime value to be formatted. The type of the expression can be localdate or time or timestamp or timestamptz or date.i18n
. Optional. Internationalization configuration. Whendate_pattern
contains the pattern of the day in the week (EEE
orEEEE
) or the name of the month (MMM
orMMMM
), 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
date |
format_date |
---|---|
Jun 29, 2005 19:19:41 |
2005.06.29 AD at 19:19:41 |
Jan 8, 2011 22:59:56 |
2011.01.08 AD at 22:59:56 |
Text between single quotes is not interpreted (see 'at'
) and is
copied to the output as it is.
Note
If date_pattern
contains single quotes ('
) and is also
surrounded by single quotes, you have to escape these quotes with
another single quote like this:
SELECT formatdate('yyyy.MM.dd G ''at'' HH:mm:ss', CURRENT_TIMESTAMP)
Example 2
SELECT date, formatdate('h:mm a', date) AS format_date
FROM v
date |
format_date |
---|---|
Jun 29, 2005 19:19:41 |
7:19 PM |
Jan 8, 2011 22:59:56 |
22:59 PM |
Example 3
SELECT date, formatdate('yyMMddHHmmss', date) AS format_date
FROM v
date |
format_date |
---|---|
Jun 29, 2005 19:19:41 |
050629191941 |
Jan 8, 2011 22:59:56 |
110108225956 |
Example 4
SELECT date, FORMATDATE('MMMM, EEEE dd, yyyy', date, 'us_pst') AS
format_date
FROM v
date |
format_date |
---|---|
Jun 29, 2005 19:19:41 |
June, Wednesday 29, 2005 |
Jan 8, 2011 22:59:56 |
January, Saturday 8, 2011 |
Example 5
SELECT date, FORMATDATE('MMMM, EEEE dd, yyyy', date, 'de') AS
format_date
FROM v
date |
format_date |
---|---|
Jun 29, 2005 19:19:41 |
Juni, Mittwoch 29, 2005 |
Jan 8, 2011 22:59:56 |
Januar, Samstag 08, 2011 |
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 datetime. The
function returns a long data-type ranging from 1 to 31.
Syntax
GETDAY( <value:intervaldaysecond> ):long
GETDAY( <value:localdate> ):long
GETDAY( <value:timestamp> ):long
GETDAY( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETDAY( <value:date> ):long
value
. Required. Datetime to retrieve the day from.
Example
SELECT date, getday(date) as day
FROM v;
date |
day |
---|---|
Jun 29, 2005 19:19:41 |
29 |
Jan 8, 2011 22:59:56 |
8 |
GETDAYOFWEEK¶
Description
The GETDAYOFWEEK
function returns the number of the day of the week
of this datetime.
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( <value:localdate> ):long
GETDAYOFWEEK( <value:timestamp> ):long
GETDAYOFWEEK( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETDAYOFWEEK( <value:date> ):long
value
. Required.
Examples
Example 1
SELECT CURRENT_DATE AS today, GETDAYOFWEEK(CURRENT_DATE);
today |
getdayofweek |
---|---|
2020-12-07 |
2 |
Example 2
SELECT CURRENT_DATE AS today, GETDAYOFWEEK(CURRENT_DATE)
CONTEXT('i18n' = 'ES_EURO');
today |
getdayofweek |
---|---|
2020-12-07 |
1 |
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 datetime.
The first day of the year is 1
.
Syntax
GETDAYOFYEAR( <value:localdate> ):long
GETDAYOFYEAR( <value:timestamp> ):long
GETDAYOFYEAR( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETDAYOFYEAR( <value:date> ):long
value
. Required.
Example
SELECT TO_LOCALDATE('dd-MM-yyyy', '01-01-2018') AS localdate
, GETDAYOFYEAR( TO_LOCALDATE('dd-MM-yyyy', '01-01-2013') ) AS day
localdate |
day |
---|---|
Jan 1, 2018 |
1 |
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( <value 1:localdate>, <value 2:localdate> ):long
GETDAYSBETWEEN( <value 1:timestamp>, <value 2:timestamp> ):long
GETDAYSBETWEEN( <value 1:timestamp>, <value 2:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETDAYSBETWEEN( <value:date> ):long
value 1
. Required.value 2
. Required.
Example
SELECT date1, date2, GETDAYSBETWEEN(date1, date2)
FROM view
date1 |
date2 |
getdaysbetween |
---|---|---|
Jan 1, 2013 0:00:00 AM |
Jan 2, 2013 0:00:00 AM |
1 |
Jan 1, 2013 0:00:00 AM |
Dec 31, 2013 0:00:00 AM |
364 |
GETHOUR¶
Description
The GETHOUR
function returns the “hour” field of a given datetime. The
function returns a long data-type, ranging from 0 (12:00 A.M.) to 23
(11:00 P.M.).
Syntax
GETHOUR( <value:intervaldaysecond> ):long
GETHOUR( <value:time> ):long
GETHOUR( <value:timestamp> ):long
GETHOUR( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETHOUR( <value:date> ):long
value
. Required. Datetime to retrieve the hour from.
Example
SELECT date, gethour(date) as hour
FROM v;
date |
hour |
---|---|
Jun 29, 2005 19:20:41 |
19 |
GETMILLISECOND¶
The GETMILLISECOND
function returns the “milliseconds” field of a
given datetime.
Syntax
GETMILLISECOND( <value:intervaldaysecond> ):long
GETMILLISECOND( <value:time> ):long
GETMILLISECOND( <value:timestamp> ):long
GETMILLISECOND( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETMILLISECOND( <value:date> ):long
value
. Required.
GETMINUTE¶
Description
The GETMINUTE
function returns the “minute” field of a given datetime.
The function returns a value of type long, ranging from 0 to 59.
Syntax
GETMINUTE( <value:intervaldaysecond> ):long
GETMINUTE( <value:time> ):long
GETMINUTE( <value:timestamp> ):long
GETMINUTE( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETMINUTE( <value:date> ):long
value
. Required. Datetime to retrieve the minute from.
Example
SELECT date, getMinute(date) as minute
FROM v;
date |
minute |
---|---|
Jun 29, 2005 19:20:41 |
20 |
GETMONTH¶
Description
The GETMONTH
function returns the number of month in a year of a
given datetime. The function returns a long data-type, ranging from 1
(January) to 12 (December).
Syntax
GETMONTH( <value:intervalyearmonth> ):long
GETMONTH( <value:localdate> ):long
GETMONTH( <value:timestamp> ):long
GETMONTH( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETMONTH( <value:date> ):long
datetime
. Required. Datetime to retrieve the number of month from.
Example
SELECT date, getMonth(date) as month
FROM v
date |
month |
---|---|
Jun 29, 2005 19:20:41 |
6 |
GETMONTHSBETWEEN¶
Description
The GETMONTHSBETWEEN
function returns the number of months between
two datetimes.
It returns 0 if both datetimes 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( <value 1:localdate>, <value 2:localdate> ):long
GETMONTHSBETWEEN( <value 1:timestamp>, <value 2:timestamp> ):long
GETMONTHSBETWEEN( <value 1:timestamptz>, <value 2:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETMONTHSBETWEEN( <value 1:date>, <value 2:date> ):long
value 1
. Required.value 1
. Required.
Example
SELECT date1, date2, GETMONTHSBETWEEN(date1, date2)
date1 |
date2 |
getmonthsbetween |
---|---|---|
Jan 1, 2013 0:00:00 AM |
Feb 1, 2013 0:00:00 AM |
1 |
Jan 1, 2013 0:00:00 AM |
Dec 31, 2013 0:00:00 AM |
11 |
Jan 1, 2013 0:00:00 AM |
Jan 15, 2013 0:00:00 AM |
0 |
GETQUARTER¶
Description
The GETQUARTER
function returns the quarter of the year of a given
datetime.
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( <value:localdate> ):long
GETQUARTER( <value:timestamp> ):long
GETQUARTER( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETQUARTER( <value:date> ):long
datetime
. Required. Datetime from which you want to retrieve its quarter.
Example
SELECT date, GETQUARTER(date) as quarter
FROM v
date |
quarter |
---|---|
Jun 29, 2015 19:20:41 |
2 |
Mar 1, 2015 00:00:00 |
1 |
GETSECOND¶
Description
The GETSECOND
function returns the “second” field of a given datetime.
The function returns a value of type long that ranges from 0 to 59.
Syntax
GETSECOND( <value:intervaldaysecond> ):long
GETSECOND( <value:time> ):long
GETSECOND( <value:timestamp> ):long
GETSECOND( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETSECOND( <value:date> ):long
value
. Required. Datetime to retrieve the second from.
Example
SELECT date, GETSECOND(date) as second
FROM v
date |
second |
---|---|
Jun 29, 2005 19:20:41 |
41 |
GETTIMEFROMMILLIS¶
Description
The GETTIMEFROMMILLIS
function returns a “timestamp with time zone” that represents the specified number of milliseconds since the standard base time known as “the epoch”, namely January 1, 1970, 00:00:00 GMT.
Syntax
GETTIMEFROMMILLIS( <value:long> ):timestamptz
value
. Required.
Example
SELECT longsample, gettimefrommillis(longsample) as date
FROM v
longsample |
date |
---|---|
0 |
Jan 01, 1970 01:00:00+01:00 |
711745200000 |
Jul 21, 1992 21:00:00+02:00 |
GETTIMEINMILLIS¶
Description
The GETTIMEINMILLIS
function returns the number of milliseconds from
January 1, 1970, 00:00:00 GMT to the datetime passed as parameter.
It returns a negative number if the datetime is prior to 1970.
Syntax
GETTIMEINMILLIS( <value:localdate> ):long
GETTIMEINMILLIS( <value:timestamp> ):long
GETTIMEINMILLIS( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETTIMEINMILLIS( <value:date> ):long
value
. Required.
Example
SELECT date, getTimeInMillis(date) as milliseconds
FROM v
date |
milliseconds |
---|---|
Jun 29, 2005 19:20:41 |
1120098041000 |
GETWEEK¶
Description
The GETWEEK
function returns the week of the year of a given datetime.
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( <value:localdate> ):long
GETWEEK( <value:timestamp> ):long
GETWEEK( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETWEEK( <value:date> ):long
value
. Required. Datetime from which you want to retrieve the week of the year.
Example
SELECT date, GETWEEK(date) as week
FROM v
Date |
week |
---|---|
Jan 01, 2016 00:00:00 |
53 |
Jan 10, 2016 00:00:00 |
1 |
Jan 11, 2016 00:00:00 |
2 |
GETYEAR¶
Description
The GETYEAR
function returns the “year” field of a given datetime.
Syntax
GETYEAR( <value:intervalyearmonth> ):long
GETYEAR( <value:localdate> ):long
GETYEAR( <value:timestamp> ):long
GETYEAR( <value:timestamptz> ):long
; Deprecated signature because it uses the date (deprecated) type
GETYEAR( <value:date> ):long
value
. Required. Datetime to retrieve the year from.
Example
SELECT date, GETYEAR(date) as year
FROM Dual();
date |
year |
---|---|
Jun 29, 2005 19:20:41 |
2005 |
LASTDAYOFMONTH¶
Description
The LASTDAYOFMONTH
function returns the datetime 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( <value:localdate> ):localdate
LASTDAYOFMONTH( <value:timestamp> ):timestamp
LASTDAYOFMONTH( <value:timestamptz> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
LASTDAYOFMONTH( <value:date> ):date
value
. Required.
Example
SELECT time, LASTDAYOFMONTH(time)
FROM v
time |
lastdayofmonth |
---|---|
Jun 30, 2005 19:19:41 |
Jun 30, 2005 19:19:41 |
Feb 12, 2011 22:59:56 |
Feb 28, 2011 22:59:56 |
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 datetime passed as parameter with
the field day rolled up to the last day of the week.
If the datetime 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( <value:localdate> ):localdate
LASTDAYOFWEEK( <value:timestamp> ):timestamp
LASTDAYOFWEEK( <value:timestamptz> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
LASTDAYOFWEEK( <value:date> ):date
value
. Required.
Example
SELECT time, LASTDAYOFWEEK(time)
FROM v
time |
lastdayofweek |
---|---|
Thursday, Jun 30, 2005 19:19:41 |
Sunday, Jul 03, 2005 19:19:41 |
Saturday, Dec 31, 2011 22:59:56 |
Sunday, Jan 1, 2012 22:59:56 |
Sunday, Jul 03, 2005 19:19:41 |
Sunday, Jul 03, 2005 19:19:41 |
LOCALTIME¶
Description
The LOCALTIME
function returns a value of type time
that represents the current time in the query time zone.
To obtain the current instant of time on a different type (e.g. localdate
, time
, etc.) see the table Function to obtain the current datetime.
This function is invoked without parenthesis. That is, use LOCALTIME
, not LOCALTIME()
.
Syntax
LOCALTIME:date
Example
SELECT LOCALTIME as time_now
FROM dual();
time_now |
---|
04:20:00.687 |
LOCALTIMESTAMP¶
Description
The LOCALTIMESTAMP
function returns a value of type timestamp
that represents the current date and time in the query time zone.
To obtain the current instant of time on a different type (e.g. localdate
, time
, etc.) see the table Function to obtain the current datetime.
This function is invoked without parenthesis. That is, use LOCALTIMESTAMP
, not LOCALTIMESTAMP()
.
Syntax
LOCALTIMESTAMP:timestamp
Example
SELECT LOCALTIMESTAMP as date_and_time_now
FROM dual();
date_and_time_now |
---|
2024-11-12 04:20:00.000 |
NEXTWEEKDAY¶
Description
The NEXTWEEKDAY
function returns this datetime with its field day rolled
up to the day of the week indicated by the parameter weekDay
.
If the parameter datetime
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( <value:localdate>, <week day:int> ):localdate
NEXTWEEKDAY( <value:timestamp>, <week day:int> ):timestamp
NEXTWEEKDAY( <value:timestamptz>, <week day:int> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
NEXTWEEKDAY( <value:date>, <week day:int> ):date
value
. Required. Datetime expression.week day
. Required. The day of the week that the datetime will be rolled up to.
Example
SELECT time, NEXTWEEKDAY(time, 3)
FROM v
time |
nextweekday |
---|---|
Thursday, Jun 30, 2005 19:19:41 |
Wednesday, Jul 6, 2005 19:19:41 |
Monday, Feb 7, 2011 22:59:56 |
Feb, Wed 9, 2011 22:59:56 |
Wednesday, Feb 9, 2011 9:37:02 |
Wednesday, Feb 16, 2011 9:37:02 |
NOW¶
Description
The NOW
function returns a value of type timestamptz
that represents the current “timestamp with time zone”.
NOW()
is equivalent to CURRENT_TIMESTAMP. CURRENT_TIMESTAMP
is the name of the function in the SQL standard.
PREVIOUSWEEKDAY¶
Description
The PREVIOUSWEEKDAY
function returns this datetime with its field day
rolled down to the day of the week indicated by the parameter
weekDay
.
If the parameter datetime
already represents the day weekDay
, the
function rolls down the datetime to the same day of previous week.
The days of the week are: Sunday = 0, Monday = 1, Tuesday = 2 …
Syntax
PREVIOUSWEEKDAY( <value:localdate>, <week day:int> ):localdate
PREVIOUSWEEKDAY( <value:timestamp>, <week day:int> ):timestamp
PREVIOUSWEEKDAY( <value:timestamptz>, <week day:int> ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
PREVIOUSWEEKDAY( <value:date>, <week day:int> ):date
value
. Required. Datetime expression.week day
. Required. The day of the week that the datetime will be rolled down to.
Example
SELECT time, previousweekday(time, 2)
FROM v
time |
previousweekday |
---|---|
Thursday, Jun 30, 2005 19:19:41 |
Tuesday, Jun 28, 2005 19:19:41 |
Monday, Feb 7, 2011 22:59:56 |
Tuesday, Feb 1, 2011 22:59:56 |
Tuesday, Jan 4, 2011 9:37:02 |
Tuesday, Dec 28, 2010 9:37:02 |
TO_DATE¶
Description
The TO_DATE
function converts a text
value containing a datetime in
a specific format, into a value of type date
that represents this
date.
Important
Avoid using this function on new projects because it is deprecated and it may be removed in the next major version of Denodo. It is deprecated because its first input parameter is of type date, which is also deprecated. Instead, use TO_LOCALDATE, TO_TIME, TO_TIMESTAMP or TO_TIMESTAMPTZ. See more about datetime values and functions in the section Data Types for Dates, Timestamps and Intervals of the VQL Guide.
The section Features Deprecated in Denodo Platform lists all the features that are deprecated.
Syntax
TO_DATE( <date pattern:text>, <value:text> [, <i18n:text> ] [, <timestamp:boolean> ] ):date
date pattern
. Required. Pattern describing the date and time format ofvalue
, following the syntax defined by Java in the classjava.text.SimpleDataFormat
(see section Date and Time Pattern Strings for more information).In order to delegate this function to a database, Virtual DataPort translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine of Virtual DataPort will execute the function instead of delegating it to the database.
value
. Required. String that contains a date following the pattern of the parameterdatePattern
.i18n
. Optional. Internationalization configuration. Whendate pattern
contains the pattern of the day in the week (EEE
orEEEE
) or the name of the month (MMM
orMMMM
), this parameter indicates the language that the function expects these values to be in. For example, ifvalue
will contain the names of the months in German, the value of this parameter has to bede
, unless the i18n of the database isde
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. Iffalse
, the function sets to0
the fields that represent the time: hour, minute, second and millisecond. Passingtrue
to this parameter is the same as not passing it.
Examples
Example 1
SELECT TO_DATE('M dd yyyy HH:mm:ss', '3 05 2020 21:17:05')
FROM Dual();
to_date |
---|
2020-03-05 21:17:05-08:00 |
Example 2
SELECT TO_DATE ('yyyyMMddHHmmss', '20200701102030')
FROM Dual();
to_date |
---|
2020-07-01 10:20:30-07:00 |
Example 3
SELECT TO_DATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2020-07-04T12:08:56.235')
FROM Dual();
to_date |
---|
2020-07-04 12:08:56.235-07:00 |
Example 4
SELECT TO_DATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2020-07-04T12:08:56.235', false)
FROM Dual();
to_date |
---|
2020-07-04 00:00:00-07:00 |
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
date_string |
to_date |
---|---|
Juni, Mittwoch 29, 2019 |
2019-06-29 00:00:00-07:00 |
Januar, Samstag 08, 2021 |
2021-01-08 00:00:00-08:00 |
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.
TO_LOCALDATE¶
Description
The TO_LOCALDATE
function converts a text
value containing a datetime in
a specific format, into a value of type localdate
that represents this
datetime.
Syntax
TO_LOCALDATE( <localdate pattern:text>, <value:text> [, <language:text> ] ):localdate
localdate pattern
. Required. Pattern describing the date and time format ofvalue
, following the syntax defined by Java in the classjava.text.SimpleDataFormat
(see section Date and Time Pattern Strings for more information).In order to delegate this function to a database, Virtual DataPort translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine of Virtual DataPort will execute the function instead of delegating it to the database.
value
. Required. String that contains a localdate following the pattern of the parameterlocaldate Pattern
.language
. Optional. Internationalization configuration. Whenlocaldate pattern
contains the pattern of the day in the week (EEE
orEEEE
) or the name of the month (MMM
orMMMM
), this parameter indicates the language that the function expects these values to be in. For example, ifvalue
will contain the names of the months in German, the value of this parameter has to bede
.The value of this parameter has to be one of the Java language names
Examples
Example 1
SELECT TO_LOCALDATE ('yyyyMMdd', '20200701')
FROM Dual();
to_localdate |
---|
2020-07-01 |
Example 2
SELECT TO_LOCALDATE('M dd yyyy HH:mm:ss', '3 05 2020 21:17:05')
FROM Dual();
to_localdate |
---|
2020-03-05 |
Example 3
SELECT TO_LOCALDATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2020-07-04T12:08:56.235')
FROM Dual();
to_localdate |
---|
2020-07-04 |
Note
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 this example, 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 contains ''T''
.
Example 4
SELECT date_string, TO_LOCALDATE('MMMM, EEEE dd, yyyy', date_string, 'de')
FROM v
date_string |
to_localdate |
---|---|
Juni, Mittwoch 29, 2005 |
Wed Jun 29 2005 |
Januar, Samstag 08, 2011 |
Sat Jan 08 2011 |
In this example, as the parameter language
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.
TO_TIME¶
Description
The TO_TIME
function converts a text
value containing a datetime in
a specific format, into a value of type time
that represents the time part of the
datetime.
Syntax
TO_TIME( <time pattern:text>, <value:text> [, <language:text> ] ):time
time pattern
. Required. Pattern describing the date and time format ofvalue
, following the syntax defined by Java in the classjava.text.SimpleDataFormat
(see section Date and Time Pattern Strings for more information).In order to delegate this function to a database, Virtual DataPort translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine of Virtual DataPort will execute the function instead of delegating it to the database.
value
. Required. String that contains a datetime following the pattern of the parametertime Pattern
.language
. Optional. Internationalization configuration. Whentime pattern
contains the pattern of the day in the week (EEE
orEEEE
) or the name of the month (MMM
orMMMM
), this parameter indicates the language that the function expects these values to be in. For example, ifvalue
will contain the names of the months in German, the value of this parameter has to bede
.The value of this parameter has to be one of the Java language names.
Examples
Example 1
SELECT TO_TIME ('HHmmss', '102030')
FROM Dual();
to_time |
---|
10:20:30 |
Example 2
SELECT TO_TIME('M dd yyyy HH:mm:ss', '3 05 2010 21:17:05')
FROM Dual();
to_time |
---|
21:17:05 |
Example 3
SELECT TO_TIME('yyyy-MM-dd''T''HH:mm:ss.SSS', '2001-07-04T12:08:56.235')
FROM Dual();
to_time |
---|
12:08:56.235 |
Note
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 this example, 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 contains ''T''
.
TO_TIMESTAMP¶
Description
The TO_TIMESTAMP
function converts a text
value containing a datetime in
a specific format, into a value of type timestamp
that represents this
datetime.
Syntax
TO_TIMESTAMP( <timestamp pattern:text>, <value:text> [, <language:text> ] ):timestamp
timestamp pattern
. Required. Pattern describing the date and time format ofvalue
, following the syntax defined by Java in the classjava.text.SimpleDataFormat
(see section Date and Time Pattern Strings for more information).In order to delegate this function to a database, Virtual DataPort translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine of Virtual DataPort will execute the function instead of delegating it to the database.
value
. Required. String that contains a datetime following the pattern of the parametertimestamp Pattern
.language
. Optional. Internationalization configuration. Whentimestamp pattern
contains the pattern of the day in the week (EEE
orEEEE
) or the name of the month (MMM
orMMMM
), this parameter indicates the language that the function expects these values to be in. For example, ifvalue
will contain the names of the months in German, the value of this parameter has to bede
.The value of this parameter has to be one of the Java language names.
Examples
Example 1
SELECT TO_TIMESTAMP('M dd yyyy HH:mm:ss', '3 05 2020 21:17:05')
FROM Dual();
to_timestamp |
---|
2020-03-05 21:17:05 |
Example 2
SELECT TO_TIMESTAMP ('yyyyMMddHHmmss', '20200701102030')
FROM Dual();
to_timestamp |
---|
2020-07-01 10:20:30 |
Example 3
SELECT TO_TIMESTAMP('yyyy-MM-dd''T''HH:mm:ss.SSS', '2021-07-04T12:08:56.235')
FROM Dual();
to_timestamp |
---|
2021-07-04 12:08:56.235 |
Example 4
SELECT date_string, TO_TIMESTAMP('MMMM, EEEE dd, yyyy', date_string, 'de')
FROM v
date_string |
to_timestamp |
---|---|
Juni, Mittwoch 29 01:24:58, 2005 |
Wed Jun 29 01:24:58.000 2005 |
Januar, Samstag 08 03:15:01, 2011 |
Sat Jan 08 03:15:01.000 2011 |
In this example, as the parameter language
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.
TO_TIMESTAMPTZ¶
Description
The TO_TIMESTAMPTZ
function converts a text
value containing a datetime in
a specific format, into a value of type timestamptz
that represents this
datetime.
Syntax
TO_TIMESTAMPTZ( <timestamptz pattern:text>, <value:text> [, <language:text> ] ):timestamptz
timestamptz pattern
. Required. Pattern describing the date and time format ofvalue
, following the syntax defined by Java in the classjava.text.SimpleDataFormat
(see section Date and Time Pattern Strings for more information).In order to delegate this function to a database, Virtual DataPort translates the pattern to the equivalent one in the underlying database. If the database does not support the pattern, the execution engine of Virtual DataPort will execute the function instead of delegating it to the database.
value
. Required. String that contains a datetime following the pattern of the parametertimestamptz Pattern
.language
. Optional. Internationalization configuration. Whentimestamptz pattern
contains the pattern of the day in the week (EEE
orEEEE
) or the name of the month (MMM
orMMMM
), this parameter indicates the language that the function expects these values to be in. For example, ifvalue
will contain the names of the months in German, the value of this parameter has to bede
.The value of this parameter has to be one of the Java language names.
Examples
Example 1
SELECT TO_TIMESTAMPTZ('M dd yyyy HH:mm:ss', '3 05 2021 21:17:05')
FROM Dual();
to_timestamptz |
---|
2021-03-05 21:17:05-08:00 |
Example 2
SELECT TO_TIMESTAMPTZ ('yyyyMMddHHmmss', '20200701102030')
FROM Dual();
to_timestamptz |
---|
Thu Jul 01 10:20:30.000 2010 +02:00 |
Example 3
SELECT TO_TIMESTAMPTZ('yyyy-MM-dd''T''HH:mm:ss.SSS', '2001-07-04T12:08:56.235')
FROM Dual();
to_timestamptz |
---|
2020-07-01 10:20:30-07:00 |
Example 4
SELECT date_string, TO_TIMESTAMPTZ('MMMM, EEEE dd, yyyy', date_string, 'de')
FROM v
date_string |
to_timestamptz |
---|---|
Juni, Mittwoch 29 01:24:58, 2005 |
Wed Jun 29 01:24:58.000 2005 +02:00 |
Januar, Samstag 08 03:15:01, 2011 |
Sat Jan 08 03:15:01.000 2011 +01:00 |
In this example, as the parameter language
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( <value:localdate> [, <pattern:text> ] ):localdate
TRUNC( <value:time> [, <pattern:text> ] ):time
TRUNC( <value:timestamp> [, <pattern:text> ] ):timestamp
TRUNC( <value:timestamptz> [, <pattern:text> ] ):timestamptz
; Deprecated signature because it uses the date (deprecated) type
TRUNC( <value:date> [, <pattern:text> ] ):date
value
. Required. Datetime to be truncated.pattern
. Thedatetime
is truncated to the unit specified by this parameter. Ifpattern
is missing,datetime
is truncated to the nearest day. The table below lists the possible values of this parameter.
Pattern |
Truncating Unit |
---|---|
CC SCC |
Century |
SYYYY YYYY YEAR SYEAR YYY YY Y |
Year |
IYYY IYY IY I |
ISO Year |
Q |
Quarter |
MONTH MON MM RM |
Month |
WW |
Same day of the week as the first day of the year |
IW |
Same day of the week as the first day of the ISO year |
W |
Same day of the week as the first day of the month |
DDD DD J |
Day |
DAY DY D |
Starting day of the week |
HH HH12 HH24 |
Hour |
MI |
Minute |
Examples
Example 1
SELECT sale_date, TRUNC(sale_date)
FROM v
sale_date |
trunc |
---|---|
2021-06-29 19:19:41 |
2021-06-29 00:00:00 |
2021-01-08 22:59:56 |
2021-01-08 00:00:00 |
As the parameter pattern is not present, the date is truncated to the day.
Example 2
SELECT sale_date, TRUNC(sale_date, 'MONTH')
FROM v
sale_date |
trunc |
---|---|
2021-06-29 19:19:41 |
2021-06-01 00:00:00 |
2021-01-08 22:59:56 |
2021-01-01 22:59:56 |
Example 3
SELECT sale_date, TRUNC(sale_date, 'Q')
FROM v
sale_date |
trunc |
---|---|
2021-06-29 19:19:41 |
2021-04-01 00:00:00 |
2021-01-08 22:59:56 |
2021-01-01 00:00:00 |
The pattern Q
means that the date will be truncated to the
quarter.