You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by db...@apache.org on 2016/12/15 00:20:02 UTC
[1/4] incubator-trafodion git commit: [TRAFODION-2285] Document
TO_DATE, TO_TIME and TO_TIMESTAMP functions
Repository: incubator-trafodion
Updated Branches:
refs/heads/master e0993dc4c -> 97ee8a768
[TRAFODION-2285] Document TO_DATE, TO_TIME and TO_TIMESTAMP functions
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/88f4a8bf
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/88f4a8bf
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/88f4a8bf
Branch: refs/heads/master
Commit: 88f4a8bf52ed1df24d2aa21b90607516869f6119
Parents: 82604a2
Author: Dave Birdsall <db...@apache.org>
Authored: Wed Dec 7 19:20:06 2016 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Wed Dec 7 19:20:06 2016 +0000
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 137 ++++++++++++++++++-
1 file changed, 136 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/88f4a8bf/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index e42da01..a99a0cb 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -201,6 +201,9 @@ corresponding second of the minute.
_num_expr_ to _datetime_expr_.
| <<timestampdiff_function,TIMESTAMPDIFF Function>> | Returns the integer value for the number of _interval-ind_
units of time between _startdate_ and _enddate_.
+| <<to_date_function,TO_DATE Function>> | Converts a character value to a date value.
+| <<to_time_function,TO_TIME Function>> | Converts a character value to a time or timestamp value.
+| <<to_timestamp_function,TO_TIMESTAMP Function>> | Converts a character value to a timestamp value.
| <<week_function,WEEK Function>> | Returns an integer value in the range 1 through 54 that represents the
corresponding week of the year.
| <<year_function,YEAR Function>> | Returns an integer value that represents the year.
@@ -7523,7 +7526,139 @@ TIMESTAMPDIFF (SQL_TSI_WEEK, DATE '2006-01-01', DATE '2006-01-09')
```
TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2004-03-01', DATE '2004-02-01')
```
-
+
+<<<
+[[to_date_function]]
+== TO_DATE Function
+
+The TO_DATE function converts a character value to a date. The optional
+second argument describes the format of the character value.
+
+```
+TO_DATE(character-expression [,format-string])
+```
+
+* `_character-expression_`
++
+is an expression that gives a character value.
+
+* `_format-string_`
++
+is one of the following character string literals:
+
+** 'YYYY-MM-DD'
+** 'MM/DD/YYYY'
+** 'DD.MM.YYYY'
+** 'YYYY-MM'
+** 'MM/DD/YYYY'
+** 'YYYY/MM/DD'
+** 'YYYYMMDD'
+** 'YY/MM/DD'
+** 'MM/DD/YY'
+** 'MM-DD-YYYY'
+** 'YYYYMM'
+** 'DD-MM-YYYY'
+** 'DD-MON-YYYY'
+** 'DDMONYYYY'
+
+If the _format-string_ argument is omitted, 'YYYY-MM-DD' is used as the
+default.
+
+[[examples_of_to_date]]
+=== Examples of TO_DATE
+
+* This function returns the date value '2016-12-07':
++
+```
+TO_DATE ('2016-12-07')
+```
+
+* This function returns the date value '2016-07-12':
++
+```
+TO_DATE ('12-07-2016', 'DD-MM-YYYY')
+```
+
+* This function returns the date value '2016-12-07':
++
+```
+TO_DATE ('07DEC2016', 'DDMONYYYY')
+```
+
+<<<
+[[to_time_function]]
+== TO_TIME Function
+
+The TO_TIME function converts a character value to a time or timestamp. The
+second argument describes the format of the character value.
+
+```
+TO_TIME(character-expression ,format-string)
+```
+
+* `_character-expression_`
++
+is an expression that gives a character value.
+
+* `_format-string_`
++
+is one of the following character string literals:
+
+** 'HH24:MI:SS'
+** 'YYYYMMDDHH24MISS'
+** 'DD.MM.YYYY:HH24.MI.SS'
+** 'YYYY-MM-DD HH24:MI:SS'
+** 'YYYYMMDD:HH24:MI:SS'
+** 'MMDDYYYY HH24:MI:SS'
+** 'MM/DD/YYYY HH24:MI:SS'
+** 'DD-MON-YYYY HH:MI:SS'
+** 'MONTH DD, YYYY, HH:MI'
+** 'DD.MM.YYYY HH24.MI.SS'
+
+[[considerations_for_to_time]]
+=== Considerations for TO_TIME
+
+If the _format-string_ is 'HH24:MI:SS', then the output data time is time. Otherwise
+the output data type is timestamp.
+
+[[examples_of_to_time]]
+=== Examples of TO_TIME
+
+* This function returns the time value '17:05:01':
++
+```
+TO_TIME ('17:05:01', 'HH24:MI:SS')
+```
+
+* This function returns the timestamp value '2016-12-07 17:05:01.000000':
++
+```
+TO_TIME ('07.12.2016:17.05.01', 'DD.MM.YYYY:HH24.MI.SS')
+```
+
+<<<
+[[to_timestamp_function]]
+== TO_TIMESTAMP Function
+
+The TO_TIMESTAMP function converts a character value to a timestamp.
+
+```
+TO_TIMESTAMP(character-expression)
+```
+
+* `_character-expression_`
++
+is an expression that gives a character value.
+
+[[examples_of_to_timestamp]]
+=== Example of TO_TIMESTAMP
+
+* This function returns the timestamp value '2016-12-07 10:01:00':
++
+```
+TO_TIMESTAMP ('2016-12-07 10:01:00')
+```
+
<<<
[[translate_function]]
== TRANSLATE Function
[2/4] incubator-trafodion git commit: Rework addressing Ming's
comments
Posted by db...@apache.org.
Rework addressing Ming's comments
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/05640f11
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/05640f11
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/05640f11
Branch: refs/heads/master
Commit: 05640f11cee3c9487c6db93322db37a2d7f524ab
Parents: 88f4a8b
Author: Dave Birdsall <db...@apache.org>
Authored: Fri Dec 9 01:17:19 2016 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Fri Dec 9 01:17:19 2016 +0000
----------------------------------------------------------------------
.../_chapters/sql_functions_and_expressions.adoc | 14 +++++++++++++-
1 file changed, 13 insertions(+), 1 deletion(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/05640f11/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index a99a0cb..5bbecf4 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -7561,6 +7561,10 @@ is one of the following character string literals:
** 'DD-MON-YYYY'
** 'DDMONYYYY'
+Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM refers to a two-digit month. MON refers to
+a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
+'OCT', 'NOV' or 'DEC'). The month may be given in lower case, upper case or any mixture.
+
If the _format-string_ argument is omitted, 'YYYY-MM-DD' is used as the
default.
@@ -7615,6 +7619,12 @@ is one of the following character string literals:
** 'MONTH DD, YYYY, HH:MI'
** 'DD.MM.YYYY HH24.MI.SS'
+Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM refers to a two-digit month. MON refers to
+a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
+'OCT', 'NOV' or 'DEC'). MONTH refers to the month spelled out. The month in either MON or MONTH form may be given in lower case, upper case or any mixture.
+
+HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
+
[[considerations_for_to_time]]
=== Considerations for TO_TIME
@@ -7648,7 +7658,9 @@ TO_TIMESTAMP(character-expression)
* `_character-expression_`
+
-is an expression that gives a character value.
+is an expression that gives a character value. The expression ia assumed to have the format YYYY-MM-DD HH:MI:SS[.FFFFFFF],
+where YYYY is a 4-digit year, MM is a 2-digit month, DD is a 2-digit day, HH is a 2-digit hours field, MI is a 2-digit
+minutes field, SS is a two-digit seconds field and FFFFFF is an optional microseconds field.
[[examples_of_to_timestamp]]
=== Example of TO_TIMESTAMP
[3/4] incubator-trafodion git commit: [TRAFODION-2285] Rework 2:
Restrict TO_DATE and TO_TIME; also bug fixes
Posted by db...@apache.org.
[TRAFODION-2285] Rework 2: Restrict TO_DATE and TO_TIME; also bug fixes
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/638328da
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/638328da
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/638328da
Branch: refs/heads/master
Commit: 638328daaf0eb31ac5dcb632b2b2f7bf8507c019
Parents: 05640f1
Author: Dave Birdsall <db...@apache.org>
Authored: Tue Dec 13 21:38:26 2016 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Tue Dec 13 21:38:26 2016 +0000
----------------------------------------------------------------------
core/sql/exp/exp_datetime.cpp | 49 ++++++++++++++++-
core/sql/exp/exp_datetime.h | 3 ++
core/sql/exp/exp_function.cpp | 8 ++-
core/sql/optimizer/BindItemExpr.cpp | 28 +++++++---
core/sql/optimizer/ItemFunc.h | 1 +
core/sql/parser/sqlparser.y | 2 +-
core/sql/regress/seabase/EXPECTED030 | 56 ++++++++++++++------
core/sql/regress/seabase/TEST030 | 10 +++-
.../sql_functions_and_expressions.adoc | 54 +++++++++----------
9 files changed, 156 insertions(+), 55 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/exp/exp_datetime.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_datetime.cpp b/core/sql/exp/exp_datetime.cpp
index d0b9f16..0a4bb3b 100644
--- a/core/sql/exp/exp_datetime.cpp
+++ b/core/sql/exp/exp_datetime.cpp
@@ -764,6 +764,25 @@ short ExpDatetime::validateDate(rec_datetime_field startField,
return 0;
}
+// returns 0 if valid time, -1 if not
+// validates hour, minute, second
+short ExpDatetime::validateTime(const char *datetimeOpData)
+{
+ int hour = datetimeOpData[0];
+ if ((hour < 0) || (hour > 23))
+ return -1;
+
+ int minute = datetimeOpData[1];
+ if ((minute < 0) || (minute > 59))
+ return -1;
+
+ int second = datetimeOpData[2];
+ if ((second < 0) || (second > 59))
+ return -1;
+
+ return 0;
+}
+
// compDatetimes() ===================================================
// This method compares two datetime values of the same subtype and
// returns a value indicating if the first value is less than (-1),
@@ -2431,6 +2450,7 @@ static NABoolean convertStrToMonth(char* &srcData, char *result,
} // for
// error
+ raiseDateConvErrorWithSrcData(copyLen,diagsArea, originalSrcData, heap);
return FALSE;
}
@@ -2548,6 +2568,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
ULng32 flags)
{
NABoolean noDatetimeValidation = (flags & CONV_NO_DATETIME_VALIDATION) != 0;
+ char * timeData = NULL; // assume no time data
+ char * origSrcData = srcData;
short year;
Lng32 srcFormat, i;
@@ -2649,6 +2671,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
dstData[8] = 0;
dstData[9] = 0;
dstData[10] = 0;
+
+ timeData = &dstData[4];
};
break;
@@ -2708,6 +2732,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
dstData[8] = 0;
dstData[9] = 0;
dstData[10] = 0;
+
+ timeData = &dstData[4];
};
break;
@@ -2785,6 +2811,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
dstData[8] = 0;
dstData[9] = 0;
dstData[10] = 0;
+
+ timeData = &dstData[4];
};
break;
@@ -2823,6 +2851,7 @@ ExpDatetime::convAsciiToDate(char *srcData,
dstData[9] = 0;
dstData[10] = 0;
+ timeData = &dstData[4];
}
break;
@@ -2904,6 +2933,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
dstData[8] = 0;
dstData[9] = 0;
dstData[10] = 0;
+
+ timeData = &dstData[4];
};
break;
@@ -2937,6 +2968,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
dstData[8] = 0;
dstData[9] = 0;
dstData[10] = 0;
+
+ timeData = &dstData[4];
};
break;
@@ -2953,6 +2986,8 @@ ExpDatetime::convAsciiToDate(char *srcData,
// the second
if (convSrcDataToDst(2, srcData, 1, &dstData[2], NULL, heap, diagsArea))
return -1;
+
+ timeData = &dstData[0];
};
break;
@@ -2985,11 +3020,23 @@ ExpDatetime::convAsciiToDate(char *srcData,
if (validateDate(REC_DATE_YEAR, REC_DATE_DAY,
dstData, NULL, FALSE,
LastDayPrevMonth)) {
- raiseDateConvErrorWithSrcData(inSrcLen,diagsArea,srcData,heap);
+ raiseDateConvErrorWithSrcData(inSrcLen,diagsArea,origSrcData,heap);
return -1;
};
}
+ // Validate the time fields of the result
+ //
+ if (timeData)
+ {
+ if (NOT noDatetimeValidation)
+ if (validateTime(timeData))
+ {
+ raiseDateConvErrorWithSrcData(inSrcLen,diagsArea,origSrcData,heap);
+ return -1;
+ }
+ }
+
// Success
//
return 0;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/exp/exp_datetime.h
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_datetime.h b/core/sql/exp/exp_datetime.h
index 17123b8..6884c78 100644
--- a/core/sql/exp/exp_datetime.h
+++ b/core/sql/exp/exp_datetime.h
@@ -170,6 +170,9 @@ NA_EIDPROC
NABoolean &LastDayPrevMonth);
NA_EIDPROC
+ static short validateTime(const char *datetimeOpData);
+
+NA_EIDPROC
short compDatetimes(char *datetimeOpData1,
char *datetimeOpData2);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/exp/exp_function.cpp
----------------------------------------------------------------------
diff --git a/core/sql/exp/exp_function.cpp b/core/sql/exp/exp_function.cpp
index 5b7c008..21b61c8 100644
--- a/core/sql/exp/exp_function.cpp
+++ b/core/sql/exp/exp_function.cpp
@@ -2668,9 +2668,13 @@ ex_expr::exp_return_type ex_function_dateformat::eval(char *op_data[],
diagsArea,
0) < 0) {
- if (diagsArea && (*diagsArea) &&
- (*diagsArea)->getNumber(DgSqlCode::ERROR_) == 0)
+ if (diagsArea &&
+ (!(*diagsArea) ||
+ ((*diagsArea) &&
+ (*diagsArea)->getNumber(DgSqlCode::ERROR_) == 0)))
{
+ // we expect convAsciiToDate to raise a diagnostic; if it
+ // didn't, raise an internal error here
ExRaiseFunctionSqlError(heap, diagsArea, EXE_INTERNAL_ERROR,
derivedFunction(),
origFunctionOperType());
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/optimizer/BindItemExpr.cpp
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp
index 566378e..e3f4112 100644
--- a/core/sql/optimizer/BindItemExpr.cpp
+++ b/core/sql/optimizer/BindItemExpr.cpp
@@ -3985,8 +3985,9 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
{
Lng32 error = 0;
- NABoolean tc = (formatType_ == FORMAT_TO_CHAR);
- NABoolean td = (formatType_ == FORMAT_TO_DATE);
+ NABoolean toChar = (formatType_ == FORMAT_TO_CHAR);
+ NABoolean toDate = (formatType_ == FORMAT_TO_DATE);
+ NABoolean toTime = (formatType_ == FORMAT_TO_TIME);
NABoolean df = ExpDatetime::isDateFormat(frmt);
NABoolean tf = ExpDatetime::isTimeFormat(frmt);
NABoolean tsf = ExpDatetime::isTimestampFormat(frmt);
@@ -4004,7 +4005,22 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
error = 1; // error 4065
}
- if (!error && tc)
+ if (toDate && NOT (df || tsf))
+ {
+ // TO_DATE requires date format or timestamp format
+ // unless we are in mode_special_4 in which case
+ // numeric format is accepted
+ if (NOT (ms4 && nf))
+ error = 1; // error 4065
+ }
+
+ if (toTime && NOT tf)
+ {
+ // TO_TIME requires time format
+ error = 1; // error 4065
+ }
+
+ if (!error && toChar)
{
// source must be datetime with to_char function
if (opType->getTypeQualifier() != NA_DATETIME_TYPE)
@@ -4015,7 +4031,7 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
error = 3; // error 4072
}
- if (!error && td)
+ if (!error && toDate)
{
// source must be char or numeric with to_date
if ((opType->getTypeQualifier() != NA_CHARACTER_TYPE) &&
@@ -4049,8 +4065,8 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA,
case 1:
{
*CmpCommon::diags() << DgSqlCode(-4065) << DgString0(formatStr_)
- << DgString1((formatType_ == FORMAT_TO_CHAR
- ? "TO_CHAR" : "TO_DATE"));
+ << DgString1((toChar ? "TO_CHAR" :
+ (toDate ? "TO_DATE" : "TO_TIME")));
bindWA->setErrStatus();
}
break;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/optimizer/ItemFunc.h
----------------------------------------------------------------------
diff --git a/core/sql/optimizer/ItemFunc.h b/core/sql/optimizer/ItemFunc.h
index 8bde61a..8454d06 100644
--- a/core/sql/optimizer/ItemFunc.h
+++ b/core/sql/optimizer/ItemFunc.h
@@ -1821,6 +1821,7 @@ public:
{
FORMAT_GENERIC = 0,
FORMAT_TO_DATE,
+ FORMAT_TO_TIME,
FORMAT_TO_CHAR
};
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/parser/sqlparser.y
----------------------------------------------------------------------
diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y
index 905eb58..f9219e7 100755
--- a/core/sql/parser/sqlparser.y
+++ b/core/sql/parser/sqlparser.y
@@ -8877,7 +8877,7 @@ datetime_misc_function : TOK_CONVERTTIMESTAMP '(' value_expression ')'
| TOK_TO_TIME '(' value_expression ',' character_string_literal ')'
{
$$ = new (PARSERHEAP())
- DateFormat($3, *$5, DateFormat::FORMAT_TO_DATE);
+ DateFormat($3, *$5, DateFormat::FORMAT_TO_TIME);
}
| TOK_TO_TIMESTAMP '(' value_expression ')'
{
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/regress/seabase/EXPECTED030
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030
index 46b33a7..7762102 100644
--- a/core/sql/regress/seabase/EXPECTED030
+++ b/core/sql/regress/seabase/EXPECTED030
@@ -151,22 +151,7 @@
2016-03-01 10:11:00.000000
--- 1 row(s) selected.
->>select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
-
-(EXPR)
---------
-
-10:23:34
-
---- 1 row(s) selected.
->>select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
-
-(EXPR)
---------
-
-10:23:34
-
---- 1 row(s) selected.
+>>
>>select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
(EXPR)
@@ -635,6 +620,45 @@ A B C
*** ERROR[8822] The statement was not prepared.
+>>select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+
+*** ERROR[4065] The format, "HH24:MI:SS", specified in the TO_DATE function is not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
+*** ERROR[4065] The format, "HH24:MI:SS", specified in the TO_DATE function is not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_date('04-DEC-2016','DDMONYYYY') from (values(1)) x(a);
+
+*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: -DEC-2016
+
+--- 0 row(s) selected.
+>>select to_time('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+
+*** ERROR[4065] The format, "YYYY-MM-DD", specified in the TO_TIME function is not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_time('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+
+*** ERROR[4065] The format, "MM/DD/YYYY HH24:MI:SS", specified in the TO_TIME function is not supported.
+
+*** ERROR[8822] The statement was not prepared.
+
+>>select to_time('01:61:01', 'HH24:MI:SS') from (values(1)) x(a);
+
+*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 01:61:01
+
+--- 0 row(s) selected.
+>>select to_date('2016-04-33 01:01:01','YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
+
+*** ERROR[8415] The provided DATE, TIME, or TIMESTAMP is not valid and cannot be converted. Source data: 2016-04-33 01:01:01
+
+--- 0 row(s) selected.
>>select * from t030t1 where to_date(c, 'YYYY-MM-DD') = '2016-03-01';
A B C
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/core/sql/regress/seabase/TEST030
----------------------------------------------------------------------
diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030
index 7aee335..268e5c6 100644
--- a/core/sql/regress/seabase/TEST030
+++ b/core/sql/regress/seabase/TEST030
@@ -43,8 +43,7 @@ select to_date('2016-03-01 10:11:12', 'YYYY-MM-DD HH24:MI:SS') from (values(1))
select to_date('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
select to_date('01-MAR-2016 10:11:12', 'DD-MON-YYYY HH:MI:SS') from (values(1)) x(a);
select to_date('March 01, 2016, 10:11', 'MONTH DD, YYYY, HH:MI') from (values(1)) x(a);
-select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
-select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+
select to_time ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
select to_time ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
@@ -114,6 +113,13 @@ select to_date('0103.2016', 'DD.MM.YYYY') from (values(1)) x(a);
select to_char('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
select to_date(20160301, 'YYYYMMDD') from (values(1)) x(a);
select to_char(date '2016-03-01', 'HH:MI:SS') from (values(1)) x(a);
+select to_date ('10:23:34', 'HH24:MI:SS') from (values(1)) x(a);
+select to_date ('10:23:34', 'HH:MI:SS') from (values(1)) x(a);
+select to_date('04-DEC-2016','DDMONYYYY') from (values(1)) x(a);
+select to_time('2016-03-01', 'YYYY-MM-DD') from (values(1)) x(a);
+select to_time('03/01/2016 10:11:12', 'MM/DD/YYYY HH24:MI:SS') from (values(1)) x(a);
+select to_time('01:61:01', 'HH24:MI:SS') from (values(1)) x(a);
+select to_date('2016-04-33 01:01:01','YYYY-MM-DD HH24:MI:SS') from (values(1)) x(a);
select * from t030t1 where to_date(c, 'YYYY-MM-DD') = '2016-03-01';
select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY:HH24:MI:SS') from (values(1)) x(a);
select to_date('01.03.2016:10:11:12', 'DD.MM.YYYY HH24:MI:SS') from (values(1)) x(a);
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/638328da/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 5bbecf4..b15d787 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -7560,13 +7560,30 @@ is one of the following character string literals:
** 'DD-MM-YYYY'
** 'DD-MON-YYYY'
** 'DDMONYYYY'
+** 'YYYYMMDDHH24MISS'
+** 'DD.MM.YYYY:HH24.MI.SS'
+** 'YYYY-MM-DD HH24:MI:SS'
+** 'YYYYMMDD:HH24:MI:SS'
+** 'MMDDYYYY HH24:MI:SS'
+** 'MM/DD/YYYY HH24:MI:SS'
+** 'DD-MON-YYYY HH:MI:SS'
+** 'MONTH DD, YYYY, HH:MI'
+** 'DD.MM.YYYY HH24.MI.SS'
Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM refers to a two-digit month. MON refers to
a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
-'OCT', 'NOV' or 'DEC'). The month may be given in lower case, upper case or any mixture.
+'OCT', 'NOV' or 'DEC'). MONTH refers to the month spelled out. The month in either MON or MONTH form may be given in lower case, upper case or any mixture.
+
+HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
If the _format-string_ argument is omitted, 'YYYY-MM-DD' is used as the
default.
+
+[[considerations_for_to_date]]
+=== Considerations for TO_DATE
+
+If the _format-string_ contains hour, minute or seconds fields, the output data type is timestamp. Otherwise,
+the output data type is date.
[[examples_of_to_date]]
=== Examples of TO_DATE
@@ -7589,11 +7606,17 @@ TO_DATE ('12-07-2016', 'DD-MM-YYYY')
TO_DATE ('07DEC2016', 'DDMONYYYY')
```
+* This function returns the timestamp value '2016-12-07 17:05:01.000000':
++
+```
+TO_DATE ('07.12.2016:17.05.01', 'DD.MM.YYYY:HH24.MI.SS')
+```
+
<<<
[[to_time_function]]
== TO_TIME Function
-The TO_TIME function converts a character value to a time or timestamp. The
+The TO_TIME function converts a character value to a time. The
second argument describes the format of the character value.
```
@@ -7608,28 +7631,11 @@ is an expression that gives a character value.
+
is one of the following character string literals:
+** 'HH:MI:SS'
** 'HH24:MI:SS'
-** 'YYYYMMDDHH24MISS'
-** 'DD.MM.YYYY:HH24.MI.SS'
-** 'YYYY-MM-DD HH24:MI:SS'
-** 'YYYYMMDD:HH24:MI:SS'
-** 'MMDDYYYY HH24:MI:SS'
-** 'MM/DD/YYYY HH24:MI:SS'
-** 'DD-MON-YYYY HH:MI:SS'
-** 'MONTH DD, YYYY, HH:MI'
-** 'DD.MM.YYYY HH24.MI.SS'
-Here, YYYY refers to a 4-digit year. YY refers to a two-digit year. MM refers to a two-digit month. MON refers to
-a three-letter abbreviation for month ('JAN', 'FEB', 'MAR', 'APR', 'MAY', 'JUN', 'JUL', 'AUG', 'SEP',
-'OCT', 'NOV' or 'DEC'). MONTH refers to the month spelled out. The month in either MON or MONTH form may be given in lower case, upper case or any mixture.
-
-HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
-[[considerations_for_to_time]]
-=== Considerations for TO_TIME
-
-If the _format-string_ is 'HH24:MI:SS', then the output data time is time. Otherwise
-the output data type is timestamp.
+Here, HH and HH24 refer to a 2-digit hour field. MI refers to a two-digit minutes field. SS refers to a 2-digit seconds field.
[[examples_of_to_time]]
=== Examples of TO_TIME
@@ -7639,12 +7645,6 @@ the output data type is timestamp.
```
TO_TIME ('17:05:01', 'HH24:MI:SS')
```
-
-* This function returns the timestamp value '2016-12-07 17:05:01.000000':
-+
-```
-TO_TIME ('07.12.2016:17.05.01', 'DD.MM.YYYY:HH24.MI.SS')
-```
<<<
[[to_timestamp_function]]
[4/4] incubator-trafodion git commit: Merge [TRAFODION-2285] PR 872
Document TO_DATE, TO_TIME, TO_TIMESTAMP
Posted by db...@apache.org.
Merge [TRAFODION-2285] PR 872 Document TO_DATE, TO_TIME, TO_TIMESTAMP
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/97ee8a76
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/97ee8a76
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/97ee8a76
Branch: refs/heads/master
Commit: 97ee8a768c9ad80392140e878d4d9c925fb3ff98
Parents: e0993dc 638328d
Author: Dave Birdsall <db...@apache.org>
Authored: Thu Dec 15 00:18:34 2016 +0000
Committer: Dave Birdsall <db...@apache.org>
Committed: Thu Dec 15 00:18:34 2016 +0000
----------------------------------------------------------------------
core/sql/exp/exp_datetime.cpp | 49 +++++-
core/sql/exp/exp_datetime.h | 3 +
core/sql/exp/exp_function.cpp | 8 +-
core/sql/optimizer/BindItemExpr.cpp | 28 +++-
core/sql/optimizer/ItemFunc.h | 1 +
core/sql/parser/sqlparser.y | 2 +-
core/sql/regress/seabase/EXPECTED030 | 56 +++++--
core/sql/regress/seabase/TEST030 | 10 +-
.../sql_functions_and_expressions.adoc | 149 ++++++++++++++++++-
9 files changed, 277 insertions(+), 29 deletions(-)
----------------------------------------------------------------------