Dateformat Method - Connect_CDC - connect_cdc_mimix_share - Latest

The built-in method “dateformat” is provided for general-purpose date formatting with two variations of this method:

dateformat(input, output_format);
dateformat(input, output_format, input_format);

Where:

• input–is a string containing a date, time, or timestamp value. By default, it is presumed to be in “Java” timestamp format: “yyyy-MM-dd HH:mm:ss.ssssss” (for example, 2010-10-08 07:01:01.123456).

• output_format–is a string containing pattern characters that dictate how the input will be formatted to produce an output string, which is the value the method returns.

• input_format–is a string containing pattern characters that dictate the format of the input string if it is NOT in “standard” Java timestamp format. For timestamp SQL column datatypes, JDBC outputs the column value in Java timestamp format (see above), so the format should be recognizable. This optional input format string is available to indicate to the method what format the data has so that the method can format it appropriately. One would use this for date and time types (used in DB2) or other strings containing time or date-related data that the user wants to format in some meaningful way.

The input and output format strings contain pattern characters and data characters that dictate how the timestamp is laid out in the string. Note that the dateformat method uses the formatting capabilities of the Java class SimpleDateFormat.

In this pattern, the following ASCII letters are reserved as pattern characters:

The number of contiguous pattern letters determines the resulting format.

• (Text): for 4 or more pattern letters, full form results; for less than 4, a short or abbreviated form results, if one exists.

• (Number): the minimum number of digits. Shorter numbers are zero-padded to this amount. Year is handled specially; that is, if the count of 'y' is 2, the Year will be truncated to 2 digits.

• (Text & Number): 3 or over, use text, otherwise use number.

Any characters in the pattern that are not in the ranges of ['a'..'z'] and ['A'..'Z'] will be treated as quoted text. For instance, characters like ':', '.', ' ', '#' and '@' will appear in the resulting time text even they are not embraced within single quotes.

A pattern containing any invalid pattern letter results in an error during expression validation.

Examples using the US Locale

Format Pattern
--------------
  ->> Result
  -------
 
"yyyy.MM.dd G 'at' hh:mm:ss z"
  ->> 1996.07.10 AD at 15:08:56 PDT
 
"EEE, MMM d, ''yy"
  ->>  Wed, July 10, '96
 
"h:mm a"
  ->> 12:08 PM
 
"hh 'o''clock' a, zzzz"
  ->>  12 o'clock PM, Pacific Daylight Time
 
"K:mm a, z"
  ->> 0:00 PM, PST
"yyyyy.MMMMM.dd GGG hh:mm aaa"
  ->> 1996.July.10 AD 12:08 PM

In the following examples, if

• Ctime = 00:00:01

• Cdate = 1996-02-21

• Ctimestamp = 1910-01-08 17:01:01.000003

Then the following expressions produce the result values that follow them:

concat('2002-07-07', '-', substring(Ctime,1));
Result Value = 2002-07-07-00:00:01
 
concat('2002-07-07', '.',substring(Ctimestamp,12));
Result Value = 2002-07-07.17:01:01.000003
 
dateformat(Ctimestamp,'yyyy-MM-HH.mm.ss.SSS') /* this is what company X wants */;
Result Value = 1910-01-17.01.01.003
 
dateformat(Cdate,'MMM','yyyy-MM-dd');
Result Value = Feb
 
dateformat(Ctimestamp,'yyyy MMMMM dd','yyyy-MM-dd hh:mm:ss');
Result Value = 1910 January 08
 
dateformat(Cdate,'MMMMM','yyyy-MM-dd');
Result Value = February
 
dateformat(Cdate,'EEE, MMMMM dd, yyyy.','yyyy-MM-dd');
Result Value = Wed, February 21, 1996.
 
dateformat(Cdate,'EEEEE, MMMMM dd, yyyy.','yyyy-MM-dd');
Result Value = Wednesday, February 21, 1996.

When parsing a date string using the abbreviated year pattern ("y" or "yy"), the format must interpret the abbreviated year relative to some century. It does this by adjusting dates to be within 80 years before and 20 years after the current time. For example, using a pattern of "MM/dd/yy" with a current time of Jan 1, 1997, the string "01/11/12" would be interpreted as Jan 11, 2012 while the string "05/04/64" would be interpreted as May 4, 1964. During parsing, only strings consisting of exactly two numeric digits are parsed into the default century. Any other numeric string, such as a one-digit string, a three or more digit string, or a two-digit string that is not all digits (for example, "-1"), is interpreted literally. Therefore, "01/02/3" or "01/02/003" are parsed, using the same pattern, as Jan 2, 3 AD. Likewise, "01/02/-3" is parsed as Jan 2, 4 BC.

If the year pattern has more than two 'y' characters, the year is interpreted literally, regardless of the number of digits. Therefore, using the pattern "MM/dd/yyyy", "01/11/12" parses to Jan 11, 12 A.D.

For time zones that have no names, use strings GMT+hours:minutes or GMT-hours:minutes.

The calendar defines what is the first day of the week, the first week of the year, whether hours are zero based or not (0 vs 12 or 24), and the time zone. One common decimal format handles all the numbers; the digit count is handled programmatically according to the pattern.

Example using dec(8) data value to a timestamp type

The following example uses a dec(8) data value in the form of yyyyMMdd to replicate to a MS SQL Server datetime target, which is a timestamp type.

The mapping is a source column dec(8) with a target column timestamp. The source contains a yyyyMMdd date and the target is a timestamp. To replicate this, you could use the following expression:

dateformat(tostring(CDATED),'yyyy-MM-dd HH:mm:ss','yyyyMMdd');

where CDATED is the source column defined as dec(8).

• Since the first parameter to dateformat is a string and the column CDATED is decimal, the tostring is needed to convert the decimal date to character.

• The second parameter must be a standard Java timestamp format so that the output of the method creates a timestamp compatible with the target column (which is a timestamp).

• The third or input format is needed to describe what format the input date is in, since it is not in a standard Java timestamp format.