Date Processing Functions¶
These functions manipulate values of type date
.
Date functions supported by Virtual DataPort
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
time |
addday |
---|---|
Jun 29, 2005 19:19:41 |
Jul 7, 2005 19:19:41 |
Dec 31, 2010 22:59:56 |
Jan 8, 2011 22:59:56 |
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
time |
addhour |
---|---|
Jun 29, 2005 19:19:41 |
Jun 29, 2005 17:19:41 |
Jun 30, 2005 1:00:00 |
Jun 29, 2005 23:00:00 |
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
Time |
addminute |
---|---|
Jun 29, 2005 19:19:41 |
Jun 29, 2005 19:29:41 |
Jun 30, 2005 22:59:00 |
Jun 30, 2005 23:09:00 |
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
time |
addmonth |
---|---|
Jun 29, 2005 19:19:41 |
Jun 29, 2004 19:19:41 |
Jan 8, 2011 22:59:56 |
Jan 8, 2010 22:59:56 |
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
time |
addsecond |
---|---|
Jun 29, 2005 19:19:41 |
Jun 29, 2005 19:19:46 |
Jun 30, 2005 22:59:56 |
Jun 30, 2005 23:00:01 |
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. If0
, it returnsdate
, unmodified.
Example
SELECT time, ADDWEEK(time, -2)
FROM v
time |
addweek |
---|---|
Jun 29, 2005 19:19:41 |
Jun 15, 2005 19:19:41 |
Jan 8, 2011 22:59:56 |
Dec 25, 2010 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 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
time |
addyear |
---|---|
Jun 29, 2005 19:19:41 |
Jun 29, 2012 19:19:41 |
Jan 8, 2011 22:59:56 |
Jan 8, 2018 22:59:56 |
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
current_date_1 |
current_date_2 |
---|---|
Oct 28, 2013 00:00:00 |
Oct 28, 2013 00:00:00 |
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 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.
date
orfield
. Required. A field of typedate
or an expression that returns adate
value.
Examples
Example 1
SELECT time, EXTRACT(YEAR FROM time) AS year
FROM view
time |
year |
---|---|
Jun 29, 2005 19:19:41 |
2005 |
Jan 1, 2012 22:59:56 |
2012 |
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
time |
next_day |
---|---|
Jun 30, 2005 19:19:41 |
1 |
Jan 1, 2012 22:59:56 |
2 |
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
time |
firstdayofmonth |
---|---|
Jun 29, 2005 19:19:41 |
Jun 1, 2005 19:19:41 |
Jan 8, 2011 22:59:56 |
Jan 1, 2011 22:59:56 |
Jan 1, 2011 22:59:56 |
Jan 1, 2011 22:59:56 |
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
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 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.
Note
If a view or a query uses the function FORMATDATE
and this
function is delegated to a database, you should only use a date pattern
(parameter date_pattern
) supported by the database.
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 functionFORMATDATE
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. 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', now())
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 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;
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 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')
Now |
getdayofweek |
---|---|
Jan 6, 2013 00:00:00 |
1 |
Example 2
SELECT NOW(), GETDAYOFWEEK(NOW())
CONTEXT('i18n' = 'ES_EURO')
now |
getdayofweek |
---|---|
Jan 6, 2013 00:00:00 |
7 |
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') )
date |
getdayofyear |
---|---|
Jan 1, 2013 00:00:00 |
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( <date1:date>, <date2:date> ):long
date1
. Required.date2
. 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 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;
date |
hour |
---|---|
Jun 29, 2005 19:20:41 |
19 |
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;
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 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
date |
month |
---|---|
Jun 29, 2005 19:20:41 |
6 |
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)
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
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
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 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
date |
second |
---|---|
Jun 29, 2005 19:20:41 |
41 |
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
date |
milliseconds |
---|---|
Jun 29, 2005 19:20:41 |
1120098041000 |
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
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 date.
Syntax
GETYEAR( <date:date> ):long
date
. Required. Date 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 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
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 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
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 |
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
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 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();
date_and_time_now |
---|
Feb 9, 2011 9:37:02 |
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
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 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 ofvalue
, following the syntax defined by Java in the classjava.text.SimpleDataFormat
(see section Date and Time Pattern Strings for more information).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 2010 21:17:05')
FROM Dual();
to_date |
---|
Fri Mar 05 21:17:05 2010 |
Example 2
SELECT TO_DATE ('yyyyMMddHHmmss', '20100701102030')
FROM Dual();
to_date |
---|
Thu Jul 01 10:20:30 2010 |
Example 3
SELECT TO_DATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2001-07-04T12:08:56.235')
FROM Dual();
to_date |
---|
Wed Jul 04 12:08:56 2001 |
Example 4
SELECT TO_DATE('yyyy-MM-dd''T''HH:mm:ss.SSS', '2001-07-04T12:08:56.235', false)
FROM Dual();
to_date |
---|
Wed Jul 04 00:00:00 2001 |
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, 2005 |
Wed Jun 29 00:00:00 2005 |
Januar, Samstag 08, 2011 |
Sat Jan 08 00:00:00 2011 |
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
. Thedate
is truncated to the unit specified by this parameter. Ifpattern
is missing,date
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 |
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:
JDBC Adapter |
Pattern that Causes the Query to Fail to a Data Source with this Adapter |
---|---|
SQL Server adapter |
CC, SCC, IYYY, IYY, IY, Y, Q, WW, IW, W, DAY, DY or D |
Amazon Redshift |
CC, SCC, IYYY, IYY, IY, Y, WW or YW |
SAP HANA |
YYYY, MM, DD, HH or MI |
Snowflake |
CC, SCC, IYYY, IYY, IY, Y, WW, W, DAY, DY or D |
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
time |
trunc |
---|---|
Jun 29, 2005 19:19:41 |
Jun 29, 2005 0:0:00 |
Jan 8, 2011 22:59:56 |
Jan 8, 2011 0:0:00 |
As the parameter pattern is not present, the date is truncated to the day.
Example 2
SELECT time, TRUNC(time, 'MONTH')
FROM v
Time |
trunc |
---|---|
Jun 29, 2005 19:19:41 |
Jun 1, 2005 0:00:00 |
Jan 8, 2011 22:59:56 |
Jan 1, 2011 0:00:00 |
Example 3
SELECT time, TRUNC(time, 'Q')
FROM v
time |
Trunc |
---|---|
Jun 29, 2005 19:19:41 |
Apr 1, 2005 00:00:00 |
Jan 8, 2011 22:59:56 |
Jan 1, 2011 00:00:00 |
The pattern Q
means that the date will be truncated to the
quarter.