On 12-Sep-2014 14:01 -0500, Stone, Joel wrote:
Is there a simpler method to accomplish?
Hopefully simpler than:
timestamp( substr(DATEMMDDYYYY, 6, 4)
||'-'||substr(DATEMMDDYYYY, 1, 1)
||'-'||substr(DATEMMDDYYYY, 3, 2)
||'-00.00.00.000000')
The above works for a single digit month (January thru September 1
thru 9).
For the 2 digit months, the above must be altered so it gets even
messier.
And again for 1 or 2 digit days (1-9 vs 10-31)
Is there a nice easy short & simple way to accomplish
m/d/yyyy or mm/d/yyyy or m/dd/yyyy or mm/dd/yyyy
to TIMESTAMP in SQL?
The SQL DATE() casting scalar supports all of those 4-year date
formats, one or two digits for the Month or Day, each recognized as
DATFMT(*USA) implicitly; given DATEMMDDYYY is typed as
[VAR]CHAR(eight_or_greater) or Unicode [VAR]GRAPHIC(eight_or_greater),
though six_or_greater for /Julian/ formats.
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscadate.htm>
_DATE_
"The DATE function returns a date from a value.
>>-DATE--(--expression--)---------------------------><
_expression_
An expression that returns a value of one of the following built-in
data types: a date, a timestamp, a character string, a graphic string,
or any numeric data type.
• If expression is a character or graphic string, it must not be
a CLOB or DBCLOB, and its value must be one of the following:
• A valid string representation of a date or timestamp. For
the valid formats of string representations of dates and timestamps, see
_String representations of datetime values_.
..."
A doc link is include later, to the above "see". The sub-topic of
that link is included just below; the doc specific to Date Strings:
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzdatestrings.htm>
_Date strings_
"A string representation of a date is a character or a Unicode graphic
string that starts with a digit and has a length of at least 6
characters. Trailing blanks can be included. _Leading zeros_ can be
omitted from the month and day portions when using the IBM® SQL standard
formats. Each IBM SQL standard format is identified by name and includes
an associated abbreviation (for use by the CHAR function). ..."
As such, to get the TIMESTAMP should be _as simple as_ any of the
following [though I expect the last shown is not possible, even if
possibly alluded as possible in the docs for TIMESTAMP_ISO]:
TIMESTAMP(DATE(DATEMMDDYYYY),TIME('00.00')) /* explicit casts */
TIMESTAMP(DATEMMDDYYYY,TIME('00.00')) /* implicit DATE casting */
TIMESTAMP(DATEMMDDYYYY,'00.00') /* and implicit TIME casting */
TIMESTAMP_FORMAT(DATEMMDDYYYY,'MM/DD/YYYY') /* 1st arg is string */
-- the literal\constant specification as 2nd arg describes format
TO_DATE(DATEMMDDYYYY,'MM/DD/YYYY') /* Syn. of prior casting scalar */
TIMESTAMP_ISO(DATE(DATEMMDDYYYY)) /* explicit cast of argument */
TIMESTAMP_ISO(DATEMMDDYYYY) /* implicit DATE casting */
CAST(DATE(DATEMMDDYYYY) AS TIMESTAMP) /* explicit casting */
CAST(DATEMMDDYYYY AS TIMESTAMP) /* implicit DATE casting */
-- this is unlikely to function for lack of a
-- similar designation of TIMESTAMP_ISO for /data-type/
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscatimestamp.htm>
_TIMESTAMP_
"The TIMESTAMP function returns a timestamp from its argument or arguments.
...
expression-2
An expression that returns a value of one of the following built-in
data types: a time, a character string, or a graphic string.
If expression-2 is a character or graphic string, it must not be a
CLOB or DBCLOB, and its value must be a valid string representation of a
time. For the valid formats of string representations of times, see
_String representations of datetime values_.
..."
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzdtstrng.htm>
_String representations of datetime values_
"Values whose data types are DATE, TIME, or TIMESTAMP are represented in
an internal form that is transparent to the user of SQL. Dates, times,
and timestamps, however, can also be represented by character or Unicode
graphic strings.
To be retrieved, a datetime value can be assigned to a string variable.
The format of the resulting string will depend on the default date
format and the default time format in effect when the statement was
prepared. The default date and time formats are set based on the date
format (DATFMT), the date separator (DATSEP), the time format (TIMFMT),
and the time separator (TIMSEP) parameters.
..."
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscatsformat.htm>
_TIMESTAMP_FORMAT_
"The TIMESTAMP_FORMAT function returns a timestamp that is based on the
interpretation of the input string using the specified format. ..."
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzscaisotmsp.htm>
_TIMESTAMP_ISO_
"Returns a timestamp value based on a date, time, or timestamp argument.
..."
<
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzcast.htm>
_CAST specification_
midrange.com
