Amazon QuickSight
User Guide

The AWS Documentation website is getting a new look!
Try it now and let us know what you think. Switch to the new look >>

You can return to the original look by selecting English in the language selector above.

parseDate

parseDate parses a string to determine if it contains a date value, and returns a standard date in the format yyyy-MM-ddTkk:mm:ss.SSSZ (using the format pattern syntax specified in Class DateTimeFormat in the Joda project documentation), for example 2015-10-15T19:11:51.003Z. This function returns all rows that contain a date in a valid format and skips any rows that don't, including rows that contain null values.

Amazon QuickSight supports dates in the range from Jan 1, 1900 00:00:00 UTC to Dec 31, 2037 23:59:59 UTC. See also Supported Date Formats.

Note

In SPICE, you can use this function in a calculated field that's added to a data set, but not in a calculated field that's added to an analysis.

Syntax

parseDate(expression, ['format'], ['time_zone'])

Arguments

expression

The expression must be a string. It can be the name of a field that uses the string data type, a literal value like '1/1/2016', or a call to another function that outputs a string.

format

(Optional) A string containing the format pattern that date_string must match. For example, if you are using a field with data like 01/03/2016, you specify the format 'MM/dd/yyyy'. If you don't specify a format, it defaults to yyyy-MM-dd. Rows whose data doesn't conform to format are skipped.

Different date formats are supported based on the type of data set used. Use the following table to see details of supported date formats.

Date Source Type Supported Date Formats

File, Amazon Athena, and Salesforce data sets

All date format patterns specified in Supported Date Formats.

Direct query of Amazon Aurora, MariaDB, and MySQL databases

  • MM/dd/yyyy

  • dd/MM/yyyy

  • yyyy/MM/dd

  • MMM/dd/yyyy

  • dd/MMM/yyyy

  • yyyy/MMM/dd

  • MM/dd/yyyy HH:mm:ss

  • dd/MM/yyyy HH:mm:ss

  • yyyy/MM/dd HH:mm:ss

  • MMM/dd/yyyy HH:mm:ss

  • dd/MMM/yyyy HH:mm:ss

  • yyyy/MMM/dd HH:mm:ss

  • MM-dd-yyyy

  • dd-MM-yyyy

  • yyyy-MM-dd

  • MMM-dd-yyyy

  • dd-MMM-yyyy

  • yyyy-MMM-dd

  • MM-dd-yyyy HH:mm:ss

  • dd-MM-yyyy HH:mm:ss

  • yyyy-MM-dd HH:mm:ss

  • MMM-dd-yyyy HH:mm:ss

  • dd-MMM-yyyy HH:mm:ss

  • yyyy-MMM-dd HH:mm:ss

Direct query of Snowflake

  • dd/MM/yyyy

  • dd/MM/yyyy HH:mm:ss

  • dd-MM-yyyy

  • dd-MM-yyyy HH:mm:ss

  • MM/dd/yyyy

  • MM/dd/yyyy HH:mm:ss

  • MM-dd-yyyy

  • MM-dd-yyyy HH:mm:ss

  • yyyy/MM/dd

  • yyyy/MM/dd HH:mm:ss

  • yyyy-MM-dd

  • yyyy-MM-dd HH:mm:ss

Direct query of Microsoft SQL Server databases

  • dd-MM-yyyy

  • MM/dd/yyyy

  • dd/MM/yyyy

  • yyyy/MM/dd

  • MMM/dd/yyyy

  • dd/MMM/yyyy

  • yyyy/MMM/dd

  • dd/MM/yyyy HH:mm:ss

  • yyyy/MM/dd HH:mm:ss

  • MMM/dd/yyyy HH:mm:ss

  • dd/MMM/yyyy HH:mm:ss

  • yyyy/MMM/dd HH:mm:ss

  • MM-dd-yyyy

  • yyyy-MM-dd

  • MMM-dd-yyyy

  • yyyy-MMM-dd

  • MM-dd-yyyy HH:mm:ss

  • dd-MM-yyyy HH:mm:ss

  • yyyy-MM-dd HH:mm:ss

  • MMM-dd-yyyy HH:mm:ss

  • dd-MMM-yyyy HH:mm:ss

  • yyyy-MMM-dd HH:mm:ss

Direct query of Amazon Redshift or PostgreSQL databases

  • MM/dd/yyyy

  • dd/MM/yyyy

  • yyyy/MM/dd

  • MMM/dd/yyyy

  • dd/MMM/yyyy

  • yyyy/MMM/dd

  • MM/dd/yyyy HH:mm:ss

  • dd/MM/yyyy HH:mm:ss

  • yyyy/MM/dd HH:mm:ss

  • MMM/dd/yyyy HH:mm:ss

  • dd/MMM/yyyy HH:mm:ss

  • yyyy/MMM/dd HH:mm:ss

  • MM-dd-yyyy

  • dd-MM-yyyy

  • yyyy-MM-dd

  • MMM-dd-yyyy

  • dd-MMM-yyyy

  • yyyy-MMM-dd

  • MM-dd-yyyy HH:mm:ss

  • dd-MM-yyyy HH:mm:ss

  • yyyy-MM-dd HH:mm:ss

  • MMM-dd-yyyy HH:mm:ss

  • dd-MMM-yyyy HH:mm:ss

  • yyyy-MMM-dd HH:mm:ss

SPICE database data sets (any DBMS)

Not supported

time_zone

(Optional) A string representing an IANA time zone.

If you don't specify a time zone, UTC is used.

Return Type

Date

Example

The following example evaluates prodDate to determine if it contains date values.

parseDate(prodDate, 'MM/dd/yyyy')

The following are the given field values.

prodDate -------- 01-01-1999 12/31/2006 1/18/1982 7/4/2010

For these field values, the following rows are returned.

12-31-2006T00:00:00.000Z 01-18-1982T00:00:00.000Z 07-04-2010T00:00:00.000Z