TO_DATE

Converts a date value from INT or VARCHAR to the DATE data type. When format is omitted, the input must match the default format yyyy-MM-dd.

Syntax

Date TO_DATE(INT time)
Date TO_DATE(VARCHAR date)
Date TO_DATE(VARCHAR date, VARCHAR format)

Parameter	Data type	Description
`time`	INT	The number of days elapsed since the epoch time January 1, 1970, 00:00:00 UTC. For example, `200` resolves to `1970-07-20`. Note This behavior applies only to Ververica Runtime (VVR) 8.0.6 and later. For VVR versions earlier than 8.0.6, the value is implicitly converted to STRING.
`date`	VARCHAR	A date string. The default format is `yyyy-MM-dd`. For example, `'2017-09-15'`.
`format`	VARCHAR	A custom date format pattern. For example, `'yyyyMMdd'` or `'MMddyyyy'`.

The time (INT) parameter behavior differs by VVR version:
- VVR 8.0.6 and later: time is interpreted as the number of days since the epoch (January 1, 1970, 00:00:00 UTC).
- VVR earlier than 8.0.6: time is implicitly converted to STRING before processing.
When the format parameter is omitted with a VARCHAR input, the default format yyyy-MM-dd applies. If the upstream data uses a different format, such as MySQL DATETIME (yyyy-MM-dd HH:mm:ss), you must explicitly specify the format parameter with the actual format. Otherwise, parsing fails and returns an incorrect result such as 1970-01-01.

The following examples use table T1 as the data source.

Table T1

date1 (INT)	date2 (VARCHAR)	date3 (VARCHAR)	date4 (VARCHAR)	date5 (VARCHAR)
200	2017-09-15	20170915	09152017	092017

Pass an integer representing the number of days since the epoch.

SELECT TO_DATE(date1) AS var1
FROM T1;

var1 (DATE)
1970-07-20 (VVR 8.0.6 and later)
0200-01-01 (VVR earlier than 8.0.6)

When the input is already in yyyy-MM-dd format, omit the format parameter.

SELECT TO_DATE(date2) AS var2
FROM T1;

var2 (DATE)
2017-09-15

Pass a format pattern that matches the structure of the input string.

SELECT TO_DATE(date3, 'yyyyMMdd') AS var3,
       TO_DATE(date4, 'MMddyyyy') AS var4,
       TO_DATE(date5, 'MMyyyy')   AS var5
FROM T1;

var3 (DATE)	var4 (DATE)	var5 (DATE)
2017-09-15	2017-09-15	2017-09-01

TO_TIMESTAMP_TZ: Converts a VARCHAR date value to TIMESTAMP based on a specified time zone.
DATE_ADD: Returns a date after adding a specified number of days.