[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[asfgit]

Github user asfgit closed the pull request at:

https://github.com/apache/incubator-trafodion/pull/1309


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[liuyu000]

Github user liuyu000 commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152719226
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
+
+* The DATEDIFF function returns the number of Sundays between _startdate_ 
and _enddate_. This function counts _enddate_ if it falls on Sunday, but 
doesn't count _startdate_ even if it does fall on a Sunday.
+
++
+For example,
+
+** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the 
specified _datepart_ is WEEK.
+
+** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the 
specified _datepart_ is WEEK.
+
+[[the_first_week_of_year]]
+ The first week of year
+
+This value cannot be specified, the default value is the week in which Jan 
1 occurs.  
+
+[[negative_or_zero]]
+ Negative or zero
+
+If _enddate_ precedes _startdate_, the return value is negative or zero.
+
+[[error]]
+ Error
+
+* The DATEDIFF function generates an error if the result is out of range 
for integer values. 
+
+** For seconds, the maximum number is equivalent to approximately 68 
years. 
+
+** For weeks, if a difference in weeks is requested, one of the two dates 
cannot precede 0001-01-07.
 
 <<<
 [[examples_of_datediff]]
 === Examples of DATEDIFF
 
-* This function returns the value of 0 because no one-second boundaries
-are crossed.
+[[date_difference_in_second]]
+ Date Difference in SECOND
+
+* This function returns the value of 0 because no one-second boundary
+is crossed.
 +
 ```
-DATEDIFF( SECOND
-, TIMESTAMP '2006-09-12 11:59:58.98'
-, TIMESTAMP '2006-09-12 11:59:58.99'
-)
+SELECT DATEDIFF( SECOND
+ , TIMESTAMP '2006-09-12 11:59:58.98'
+ , TIMESTAMP '2006-09-12 11:59:58.99'
+   )
+FROM DUAL;
 ```
 
 * This function returns the value 1 because a one-second boundary is
 crossed even though the two timestamps differ by only one microsecond.
 +
 ```
-DATEDIFF( SECOND
-, 

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[liuyu000]

Github user liuyu000 commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152718499
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
+
+* The DATEDIFF function returns the number of Sundays between _startdate_ 
and _enddate_. This function counts _enddate_ if it falls on Sunday, but 
doesn't count _startdate_ even if it does fall on a Sunday.
+
++
+For example,
+
+** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the 
specified _datepart_ is WEEK.
+
+** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the 
specified _datepart_ is WEEK.
+
+[[the_first_week_of_year]]
+ The first week of year
+
+This value cannot be specified, the default value is the week in which Jan 
1 occurs.  
+
+[[negative_or_zero]]
+ Negative or zero
+
+If _enddate_ precedes _startdate_, the return value is negative or zero.
+
+[[error]]
+ Error
+
+* The DATEDIFF function generates an error if the result is out of range 
for integer values. 
+
+** For seconds, the maximum number is equivalent to approximately 68 
years. 
+
+** For weeks, if a difference in weeks is requested, one of the two dates 
cannot precede 0001-01-07.
 
 <<<
 [[examples_of_datediff]]
 === Examples of DATEDIFF
 
-* This function returns the value of 0 because no one-second boundaries
-are crossed.
+[[date_difference_in_second]]
+ Date Difference in SECOND
+
+* This function returns the value of 0 because no one-second boundary
+is crossed.
 +
 ```
-DATEDIFF( SECOND
-, TIMESTAMP '2006-09-12 11:59:58.98'
-, TIMESTAMP '2006-09-12 11:59:58.99'
-)
+SELECT DATEDIFF( SECOND
+ , TIMESTAMP '2006-09-12 11:59:58.98'
+ , TIMESTAMP '2006-09-12 11:59:58.99'
+   )
+FROM DUAL;
 ```
 
 * This function returns the value 1 because a one-second boundary is
 crossed even though the two timestamps differ by only one microsecond.
 +
 ```
-DATEDIFF( SECOND
-, 

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[liuyu000]

Github user liuyu000 commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152715161
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
+
+* The DATEDIFF function returns the number of Sundays between _startdate_ 
and _enddate_. This function counts _enddate_ if it falls on Sunday, but 
doesn't count _startdate_ even if it does fall on a Sunday.
+
++
+For example,
+
+** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the 
specified _datepart_ is WEEK.
+
+** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the 
specified _datepart_ is WEEK.
+
+[[the_first_week_of_year]]
+ The first week of year
+
+This value cannot be specified, the default value is the week in which Jan 
1 occurs.  
--- End diff --

Thanks Dave, your comments have been incorporated. :)


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[liuyu000]

Github user liuyu000 commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152715132
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
--- End diff --

Thanks Dave, your comments have been incorporated. :)


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[DaveBirdsall]

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152657575
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2297,19 +2297,22 @@ DATEADD(DAY, 7, timestamp'2008-02-29 00:00:00')
 == DATEDIFF Function
 
 The DATEDIFF function returns the integer value for the number of
-_datepart_ units of time between _startdate_ and _enddate_. If
-_enddate_ precedes _startdate_, the return value is negative or zero.
+_datepart_ units of time between _startdate_ and _enddate_. 
 
 DATEDIFF is a {project-name} SQL extension.
 
 ```
 DATEDIFF (datepart, startdate, enddate)
 ```
 
+<<<
+[[syntax_description_of_datediff]]
+=== Syntax Description of DATEDIFF 
+
 * `datepart`
 +
 is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, or one of the
-following abbreviations:
+following abbreviations, should be enclosed in quotation marks:
--- End diff --

Finished research. Verified that the abbreviations do not take quotes.


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[DaveBirdsall]

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152651211
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
+
+* The DATEDIFF function returns the number of Sundays between _startdate_ 
and _enddate_. This function counts _enddate_ if it falls on Sunday, but 
doesn't count _startdate_ even if it does fall on a Sunday.
+
++
+For example,
+
+** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the 
specified _datepart_ is WEEK.
+
+** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the 
specified _datepart_ is WEEK.
+
+[[the_first_week_of_year]]
+ The first week of year
+
+This value cannot be specified, the default value is the week in which Jan 
1 occurs.  
+
+[[negative_or_zero]]
+ Negative or zero
+
+If _enddate_ precedes _startdate_, the return value is negative or zero.
+
+[[error]]
+ Error
+
+* The DATEDIFF function generates an error if the result is out of range 
for integer values. 
+
+** For seconds, the maximum number is equivalent to approximately 68 
years. 
+
+** For weeks, if a difference in weeks is requested, one of the two dates 
cannot precede 0001-01-07.
--- End diff --

Possible wordsmith: "For weeks, the dates must be later than 0001-01-07."


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[DaveBirdsall]

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152649487
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2297,19 +2297,22 @@ DATEADD(DAY, 7, timestamp'2008-02-29 00:00:00')
 == DATEDIFF Function
 
 The DATEDIFF function returns the integer value for the number of
-_datepart_ units of time between _startdate_ and _enddate_. If
-_enddate_ precedes _startdate_, the return value is negative or zero.
+_datepart_ units of time between _startdate_ and _enddate_. 
 
 DATEDIFF is a {project-name} SQL extension.
 
 ```
 DATEDIFF (datepart, startdate, enddate)
 ```
 
+<<<
+[[syntax_description_of_datediff]]
+=== Syntax Description of DATEDIFF 
+
 * `datepart`
 +
 is YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, QUARTER, WEEK, or one of the
-following abbreviations:
+following abbreviations, should be enclosed in quotation marks:
--- End diff --

I am not sure this is correct. I tried using MI, for example, and found 
that with quotes I get a syntax error, but without quotes, it works. I am 
researching this issue.


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[DaveBirdsall]

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152650488
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
--- End diff --

Possible wordsmith: "The first day of the week is Sunday. Changing the 
first day of the week is not supported."


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[DaveBirdsall]

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152650793
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
+
+* The DATEDIFF function returns the number of Sundays between _startdate_ 
and _enddate_. This function counts _enddate_ if it falls on Sunday, but 
doesn't count _startdate_ even if it does fall on a Sunday.
+
++
+For example,
+
+** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the 
specified _datepart_ is WEEK.
+
+** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the 
specified _datepart_ is WEEK.
+
+[[the_first_week_of_year]]
+ The first week of year
+
+This value cannot be specified, the default value is the week in which Jan 
1 occurs.  
--- End diff --

Possible wordsmith: "The first week of the year is the week in which 
January 1 occurs. Changing the first week of the year is not supported."


---

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[DaveBirdsall]

Github user DaveBirdsall commented on a diff in the pull request:


https://github.com/apache/incubator-trafodion/pull/1309#discussion_r152651447
  
--- Diff: 
docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc ---
@@ -2325,82 +2328,206 @@ following abbreviations:
 
 * `startdate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
 * `enddate`
 +
-may be of type DATE or TIMESTAMP.
+may be of type DATE or TIMESTAMP, should be enclosed in quotation marks.
 See <>.
 
-The method of counting crossed boundaries such as days, minutes, and
-seconds makes the result given by DATEDIFF consistent across all data
-types. The result is a signed integer value equal to the number of
-datepart boundaries crossed between the first and second date.
-
-For example, the number of weeks between Sunday, January 4, and Sunday,
-January 1 , is 1. The number of months between March 31 and April 1
-would be 1 because the month boundary is crossed from March to April.
-The DATEDIFF function generates an error if the result is out of range
-for integer values. For seconds, the maximum number is equivalent to
-approximately 68 years. The DATEDIFF function generates an error if a
-difference in weeks is requested and one of the two dates precedes
-January 7 of the year 0001.
+[[considerations_for_datediff]]
+=== Considerations for DATEDIFF 
+
+[[boundary]]
+ Boundary
+
+The method of counting crossed boundaries such as days, minutes, and 
seconds makes the result given by DATEDIFF consistent across all data types. 
+
+The result is a signed integer value equal to the number of _datepart_ 
boundaries crossed between _startdate_ and _enddate_, because the DATEDIFF 
function does not calculate the full _datepart_, it counts the difference 
between _startdate_  and _enddate_.
+
+For example:  
+
+* The difference between 2017-12-31 23:59:59 and 2018-01-01 00:00:00 is 
only a single second. 
+
++
+However, the DATEDIFF difference is 1 minute, 1 hour, 1 day, 0 week^1^, 1 
month, 1 quarter or 1 month depending on the specified _datepart_.
+
++
+^1^ NOTE: 2017-12-31 (_startdate_) falls on Sunday which cannot be 
counted. For more information, see <>.
+
+* Likewise, the difference between 2017-01-01 and 2018-12-31 is almost two 
years.
+
++
+However, the DATEDIFF difference is 1 year if the specified _datepart_ is 
YEAR.
+
+[[the_first_day_of_week]]
+ The first day of week
+
+* This value cannot be specified, the default value is Sunday.
+
+* The DATEDIFF function returns the number of Sundays between _startdate_ 
and _enddate_. This function counts _enddate_ if it falls on Sunday, but 
doesn't count _startdate_ even if it does fall on a Sunday.
+
++
+For example,
+
+** The DATEDIFF difference is 1 between 2017-11-19 and 2017-11-26 if the 
specified _datepart_ is WEEK.
+
+** The DATEDIFF difference is 0 between 2017-11-19 and 2017-11-25 if the 
specified _datepart_ is WEEK.
+
+[[the_first_week_of_year]]
+ The first week of year
+
+This value cannot be specified, the default value is the week in which Jan 
1 occurs.  
+
+[[negative_or_zero]]
+ Negative or zero
+
+If _enddate_ precedes _startdate_, the return value is negative or zero.
+
+[[error]]
+ Error
+
+* The DATEDIFF function generates an error if the result is out of range 
for integer values. 
+
+** For seconds, the maximum number is equivalent to approximately 68 
years. 
+
+** For weeks, if a difference in weeks is requested, one of the two dates 
cannot precede 0001-01-07.
 
 <<<
 [[examples_of_datediff]]
 === Examples of DATEDIFF
 
-* This function returns the value of 0 because no one-second boundaries
-are crossed.
+[[date_difference_in_second]]
+ Date Difference in SECOND
+
+* This function returns the value of 0 because no one-second boundary
+is crossed.
 +
 ```
-DATEDIFF( SECOND
-, TIMESTAMP '2006-09-12 11:59:58.98'
-, TIMESTAMP '2006-09-12 11:59:58.99'
-)
+SELECT DATEDIFF( SECOND
+ , TIMESTAMP '2006-09-12 11:59:58.98'
+ , TIMESTAMP '2006-09-12 11:59:58.99'
+   )
+FROM DUAL;
 ```
 
 * This function returns the value 1 because a one-second boundary is
 crossed even though the two timestamps differ by only one microsecond.
 +
 ```
-DATEDIFF( SECOND
-, 

[GitHub] incubator-trafodion pull request #1309: [TRAFODION-2815] Add/Update Syntax, ...

[liuyu000]

GitHub user liuyu000 opened a pull request:

https://github.com/apache/incubator-trafodion/pull/1309

[TRAFODION-2815] Add/Update Syntax, Consideration and Example of *DATEDIFF 
Function* for *Trafodion SQL Reference Manual*

-

You can merge this pull request into a Git repository by running:

$ git pull https://github.com/liuyu000/incubator-trafodion DATEDIFF

Alternatively you can review and apply these changes as the patch at:

https://github.com/apache/incubator-trafodion/pull/1309.patch

To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:

This closes #1309


commit 1df5707278afb5944e3cec04dee82c924cd339a0
Author: liu.yu 
Date:   2017-11-22T03:50:44Z

Add/Update Syntax, Consideration and Example for *DATEDIFF Function* for 
*Trafodion SQL Reference Manual*




---