Use DATE_FORMAT to convert date and timestamp formats - Realtime Compute for Apache Flink

Limits

DATE_FORMAT is supported only in Realtime Compute for Apache Flink that uses Ververica Runtime (VVR) 3.0.0 or later.

Syntax

VARCHAR DATE_FORMAT(TIMESTAMP time, VARCHAR to_format)
VARCHAR DATE_FORMAT(VARCHAR date, VARCHAR to_format)
VARCHAR DATE_FORMAT(VARCHAR date, VARCHAR from_format, VARCHAR to_format)

Use the two-parameter signature when the input is a TIMESTAMP or a VARCHAR date in the default format (yyyy-MM-dd HH:mm:ss). Use the three-parameter signature when the source VARCHAR date uses a non-default format.

Input parameters

Parameter	Data type	Description
`time`	TIMESTAMP	The source timestamp.
`date`	VARCHAR	The source date string. Default format: `yyyy-MM-dd HH:mm:ss`.
`from_format`	VARCHAR	The format of the source date string. Optional. Default: `yyyy-MM-dd hh:mm:ss`.
`to_format`	VARCHAR	The target format for the output string.

Note

If any input parameter is NULL or a parsing error occurs, the function returns NULL.

Examples

Quick reference

-- Format a TIMESTAMP literal
SELECT DATE_FORMAT(TIMESTAMP '2017-09-15 23:00:00', 'yyMMdd');
-- Result: 170915

-- Reformat a date string with a non-default source format
SELECT DATE_FORMAT('0915-2017', 'MMdd-yyyy', 'yyyyMMdd');
-- Result: 20170915

Full example

Test data

date1 (VARCHAR)	datetime1 (VARCHAR)	nullstr (VARCHAR)
0915-2017	2017-09-15 00:00:00	NULL

Test statement

SELECT DATE_FORMAT(datetime1, 'yyMMdd') as var1,
       DATE_FORMAT(nullstr, 'yyMMdd') as var2,
       DATE_FORMAT(datetime1, nullstr) as var3,
       DATE_FORMAT(date1, 'MMdd-yyyy', nullstr) as var4,
       DATE_FORMAT(date1, 'MMdd-yyyy', 'yyyyMMdd') as var5,
       DATE_FORMAT(TIMESTAMP '2017-09-15 23:00:00', 'yyMMdd') as var6
FROM T1;

Test result

var1 (VARCHAR)	var2 (VARCHAR)	var3 (VARCHAR)	var4 (VARCHAR)	var5 (VARCHAR)	var6 (VARCHAR)
170915	null	null	null	20170915	170915

var2: NULL because nullstr is NULL.
var3: NULL because to_format (nullstr) is NULL.
var4: NULL because to_format (nullstr) is NULL.
var5: date1 (0915-2017) is parsed using from_format (MMdd-yyyy) and reformatted as yyyyMMdd.