Menu
Amazon QuickSight
User Guide

parseDate Function

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 do not.

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

parseDate is not supported for use with SPICE data sets.

Syntax

Copy
parseDate(expression, ['format'])

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 would specify the format 'MM/dd/yyyy'. If you do not 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, 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

Return Type

Date

Example

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

Copy
parseDate(Field2, 'MM/dd/yyyy')

For the following field values:

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

The following rows are returned:

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