Repository: incubator-trafodion Updated Branches: refs/heads/master e203d42a1 -> 1c26c9335
[TRAFODION-2394] Add documentation for TO_CHAR + fix some bugs in it Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/93bd6bcd Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/93bd6bcd Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/93bd6bcd Branch: refs/heads/master Commit: 93bd6bcd1c24c779a45af55bb31898a2f1f01e2f Parents: c0e92d8 Author: Dave Birdsall <[email protected]> Authored: Thu Dec 22 22:10:04 2016 +0000 Committer: Dave Birdsall <[email protected]> Committed: Thu Dec 22 22:10:04 2016 +0000 ---------------------------------------------------------------------- core/sql/exp/exp_datetime.cpp | 23 ++++- core/sql/exp/exp_datetime.h | 18 ++++ core/sql/optimizer/BindItemExpr.cpp | 30 +++++- core/sql/parser/sqlparser.y | 2 +- core/sql/regress/seabase/EXPECTED030 | 26 +++++- core/sql/regress/seabase/TEST030 | 4 + .../sql_functions_and_expressions.adoc | 98 +++++++++++++++++++- 7 files changed, 189 insertions(+), 12 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/core/sql/exp/exp_datetime.cpp ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_datetime.cpp b/core/sql/exp/exp_datetime.cpp index 0a4bb3b..8f0e677 100644 --- a/core/sql/exp/exp_datetime.cpp +++ b/core/sql/exp/exp_datetime.cpp @@ -155,10 +155,12 @@ const ExpDatetime::DatetimeFormatInfo ExpDatetime::datetimeFormat[] = {ExpDatetime::DATETIME_FORMAT_TS8, "DD-MON-YYYY HH:MI:SS", 20, 20}, {ExpDatetime::DATETIME_FORMAT_TS9, "MONTH DD, YYYY, HH:MI", 19, 25}, {ExpDatetime::DATETIME_FORMAT_TS10, "DD.MM.YYYY HH24.MI.SS", 19, 19}, - + {ExpDatetime::DATETIME_FORMAT_NUM1, "99:99:99:99", 11, 11}, - {ExpDatetime::DATETIME_FORMAT_NUM2, "-99:99:99:99", 12, 12} + {ExpDatetime::DATETIME_FORMAT_NUM2, "-99:99:99:99", 12, 12}, + // formats that are replaced by one of the other formats at bind time + {ExpDatetime::DATETIME_FORMAT_UNSPECIFIED, "UNSPECIFIED", 11, 11} }; ExpDatetime::ExpDatetime() @@ -3567,8 +3569,21 @@ ExpDatetime::convDatetimeToASCII(char *srcData, dstDataPtr++; } - str_cpy_all(dstDataPtr, "00:00:00", 8); - dstDataPtr += 8; + if (format == DATETIME_FORMAT_TS1) + { + str_cpy_all(dstDataPtr, "000000", 6); + dstDataPtr += 6; + } + else if (format == DATETIME_FORMAT_EUROPEAN) + { + str_cpy_all(dstDataPtr, "00.00.00", 8); + dstDataPtr += 8; + } + else + { + str_cpy_all(dstDataPtr, "00:00:00", 8); + dstDataPtr += 8; + } } // Return the actual number of bytes formatted. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/core/sql/exp/exp_datetime.h ---------------------------------------------------------------------- diff --git a/core/sql/exp/exp_datetime.h b/core/sql/exp/exp_datetime.h index 6884c78..fb15dec 100644 --- a/core/sql/exp/exp_datetime.h +++ b/core/sql/exp/exp_datetime.h @@ -94,6 +94,13 @@ public: DATETIME_FORMAT_NUM2, // -99:99:99:99 DATETIME_FORMAT_MAX_NUM = DATETIME_FORMAT_NUM2, + // the following are intended for binder time resolution based + // on operand type to one of the formats above + DATETIME_FORMAT_MIN_UNRESOLVED = DATETIME_FORMAT_MAX_NUM, + DATETIME_FORMAT_UNSPECIFIED, // Default format for TO_CHAR; resolved at bind time + // based on the datatype of the operand + DATETIME_FORMAT_MAX_UNRESOLVED = DATETIME_FORMAT_UNSPECIFIED, + DATETIME_FORMAT_DATE_STR, // format in str DATETIME_FORMAT_TIME_STR, // format in str DATETIME_FORMAT_NONE, @@ -353,6 +360,17 @@ static } } + for (Lng32 i = DATETIME_FORMAT_MIN_UNRESOLVED; i <= DATETIME_FORMAT_MAX_UNRESOLVED; i++) + { + if (strcmp(formatStr, datetimeFormat[i].str) == 0) + { + if (datetimeFormat[i].format != i) + return -1; + + return i; + } + } + return -1; } http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/core/sql/optimizer/BindItemExpr.cpp ---------------------------------------------------------------------- diff --git a/core/sql/optimizer/BindItemExpr.cpp b/core/sql/optimizer/BindItemExpr.cpp index e0a97fc..bee003b 100644 --- a/core/sql/optimizer/BindItemExpr.cpp +++ b/core/sql/optimizer/BindItemExpr.cpp @@ -4034,6 +4034,11 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA, // cannot convert date source to time format else if (tf && (opType->getPrecision() == SQLDTCODE_DATE)) error = 3; // error 4072 + + // cannot convert time source to date format or timestamp format + // for TO_CHAR only (for DATEFORMAT it is OK) + else if ((df || tsf) && (!wasDateformat_) && (opType->getPrecision() == SQLDTCODE_TIME)) + error = 8; // error 4072 } if (!error && toDate) @@ -4078,14 +4083,14 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA, case 2: { - *CmpCommon::diags() << DgSqlCode(-4071) << DgString0("TO_CHAR"); + *CmpCommon::diags() << DgSqlCode(-4071) << DgString0(wasDateformat_ ? "DATEFORMAT" : "TO_CHAR"); bindWA->setErrStatus(); } break; case 3: { - *CmpCommon::diags() << DgSqlCode(-4072) << DgString0("TO_CHAR") << DgString1("time");; + *CmpCommon::diags() << DgSqlCode(-4072) << DgString0("TO_CHAR") << DgString1("time"); bindWA->setErrStatus(); } break; @@ -4118,6 +4123,13 @@ NABoolean DateFormat::errorChecks(Lng32 frmt, BindWA *bindWA, } break; + case 8: + { + *CmpCommon::diags() << DgSqlCode(-4072) << DgString0("TO_CHAR") << DgString1("date"); + bindWA->setErrStatus(); + } + break; + } // switch return TRUE; @@ -4157,8 +4169,21 @@ ItemExpr * DateFormat::bindNode(BindWA * bindWA) return this; const NAType *naType0 = &child(0)->getValueId().getType(); + const DatetimeType* operand = (DatetimeType *)naType0; const NumericType * nType0 = NULL; + // if the date time format was not specified in TO_CHAR, supply a + // default now based on the datatype of the first operand + if (frmt_ == ExpDatetime::DATETIME_FORMAT_UNSPECIFIED) + { + if ((naType0->getTypeQualifier() == NA_DATETIME_TYPE) && + (operand->getPrecision() == SQLDTCODE_TIME)) + frmt_ = ExpDatetime::DATETIME_FORMAT_TS4; + else + frmt_ = ExpDatetime::DATETIME_FORMAT_DEFAULT; + formatStr_ = ExpDatetime::getDatetimeFormatStr(frmt_); + } + // a quick optimization for the date format. ItemExpr *newNode = quickDateFormatOpt(bindWA); if (newNode) @@ -4179,7 +4204,6 @@ ItemExpr * DateFormat::bindNode(BindWA * bindWA) if ((wasDateformat_) && (naType0->getTypeQualifier() == NA_DATETIME_TYPE)) { - const DatetimeType* operand = (DatetimeType *)naType0; if (operand->getPrecision() == SQLDTCODE_TIMESTAMP) { if (frmt_ == ExpDatetime::DATETIME_FORMAT_DEFAULT) http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/core/sql/parser/sqlparser.y ---------------------------------------------------------------------- diff --git a/core/sql/parser/sqlparser.y b/core/sql/parser/sqlparser.y index 9cd6063..0245aa5 100755 --- a/core/sql/parser/sqlparser.y +++ b/core/sql/parser/sqlparser.y @@ -8869,7 +8869,7 @@ datetime_misc_function : TOK_CONVERTTIMESTAMP '(' value_expression ')' | TOK_TO_CHAR '(' value_expression ')' { $$ = new (PARSERHEAP()) DateFormat - ($3, "YYYY-MM-DD", DateFormat::FORMAT_TO_CHAR); + ($3, "UNSPECIFIED", DateFormat::FORMAT_TO_CHAR); } | TOK_TO_DATE '(' value_expression ',' character_string_literal ')' http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/core/sql/regress/seabase/EXPECTED030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/EXPECTED030 b/core/sql/regress/seabase/EXPECTED030 index 7762102..68fab15 100644 --- a/core/sql/regress/seabase/EXPECTED030 +++ b/core/sql/regress/seabase/EXPECTED030 @@ -278,7 +278,7 @@ (EXPR) -------------- -2016030100:00: +20160301000000 --- 1 row(s) selected. >>select to_char(DATE '2016-03-01', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) >>x(a); @@ -620,6 +620,18 @@ A B C *** ERROR[8822] The statement was not prepared. +>>select to_char(time '10:23:34', 'YYYY-MM-DD') from (values(1)) x(a); + +*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a date. + +*** ERROR[8822] The statement was not prepared. + +>>select to_char(time '10:23:34', 'DD.MM.YYYY HH24.MI.SS') from (values(1)) x(a); + +*** ERROR[4072] The operand of function TO_CHAR must be a datetime containing a date. + +*** 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. @@ -679,6 +691,18 @@ A B C *** ERROR[8822] The statement was not prepared. +>>select to_char(3, 'HH:MI:SS') from (values(1)) x(a); + +*** ERROR[4071] The first operand of function TO_CHAR must be a datetime. + +*** ERROR[8822] The statement was not prepared. + +>>select dateformat(3, usa) from (values(1)) x(a); + +*** ERROR[4071] The first operand of function DATEFORMAT must be a datetime. + +*** ERROR[8822] The statement was not prepared. + >> >>-- some formats only enabled in special mode. Not externalized. >>cqd mode_special_4 'ON'; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/core/sql/regress/seabase/TEST030 ---------------------------------------------------------------------- diff --git a/core/sql/regress/seabase/TEST030 b/core/sql/regress/seabase/TEST030 index 268e5c6..6a79973 100644 --- a/core/sql/regress/seabase/TEST030 +++ b/core/sql/regress/seabase/TEST030 @@ -113,6 +113,8 @@ 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_char(time '10:23:34', 'YYYY-MM-DD') from (values(1)) x(a); +select to_char(time '10:23:34', 'DD.MM.YYYY HH24.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); @@ -123,6 +125,8 @@ select to_date('2016-04-33 01:01:01','YYYY-MM-DD HH24:MI:SS') from (values(1)) x 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); +select to_char(3, 'HH:MI:SS') from (values(1)) x(a); +select dateformat(3, usa) from (values(1)) x(a); -- some formats only enabled in special mode. Not externalized. cqd mode_special_4 'ON'; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/93bd6bcd/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 0b5257c..ef05776 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 @@ -202,9 +202,10 @@ corresponding second of the minute. | <<timestampadd_function,TIMESTAMPADD Function>> | Adds the interval of time specified by _interval-ind_ and _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. +units of time between _startdate_ and _enddate_. +| <<to_char_function,TO_CHAR Function>> | Converts a datetime value to a character value. +| <<to_date_function,TO_DATE Function>> | Converts a character value to a date or timestamp value. +| <<to_time_function,TO_TIME Function>> | Converts a character value to a time 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. @@ -7571,6 +7572,97 @@ TIMESTAMPDIFF (SQL_TSI_DAY, DATE '2004-03-01', DATE '2004-02-01') ``` <<< +[[to_char_function]] +== TO_CHAR Function + +The TO_CHAR function converts a datetime value to a character value. The optional +second argument describes the format of the character value. + +``` +TO_CHAR(character-expression [,format-string]) +``` + +* `_character-expression_` ++ +is an expression that gives a datetime 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' +** '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. In the output value, the month abbreviation or month +will appear in upper case. + +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 for date and timestamp values, and 'HH:MI:SS' is used for time values. + +[[considerations_for_to_char]] +=== Considerations for TO_CHAR + +If the _format-string_ contains only hour, minute or seconds fields, the input data type must be time or timestamp. + +If the _format-string_ contains only year, month or day fields, the input data type must be date or timestamp. + +If the _format-string_ contains all fields, and the input data type is date, the hour, minute and second fields in the result will be filled with zeroes. + +[[examples_of_to_char]] +=== Examples of TO_CHAR + +* This function returns the character value '01MAR2016': ++ +``` +TO_CHAR (DATE '2016-03-01', 'DDMONYYYY') +``` + +* This function returns the character value '01.03.2016 00:00:00': ++ +``` +TO_CHAR (DATE '2016-03-01', 'DD.MM.YYYY HH24.MI.SS') +``` + +* This function returns the character value '12:05:10': ++ +``` +TO_CHAR (TIME '12:05:10') +``` + +* This function returns the character value '20160301120510': ++ +``` +TO_CHAR (TIMESTAMP '2016-03-01 12:05:10','YYYYMMDDHH24MISS') +``` + +<<< [[to_date_function]] == TO_DATE Function
