PARSEDATE and FORMATDATE Functions

You can convert a date to text format, for example converting a date to a string format. The conversion is called formatting and you can use the FORMATDATE function. You can also do the opposite—convert a string into a date format to obtain a date object, the conversion is called parsing and the PARSEDATE function is used

PARSEDATE function

Use the PARSEDATE function to convert a custom date or a date appearing as a string to the standard date format. For example, to map a date field appearing as a string, use the PARSEDATE function to convert the string into a date format, and then map the field.

Syntax: PARSEDATE(dateString,pattern)

For example, you have a date in your data appearing as a string type ‘d-M-YYY’ = ’1-1-2022’.

Formula:PARSEDATE(‘1-1-2022’,’dd-MM-yyy’)

Output: The type is changed to Date and you can now map your date field.

FORMATDATE Function

Use the FORMATDATE function to convert a timestamp into a different time format. For example, you want to extract specific information from the date such as the month and year.

Syntax: FORMATDATE(date,pattern)

For example, you want to extract the month and year from the date 26/12/2022 using the pattern MM yyyy

Formula: FORMATDATE(PARSEDATE(26/12/2022,dd MM yyyy),MM yyyy)

Output:12 2022

Note After a date is converted into a string, it can no longer be mapped to a date type dimension.