Convert string to date format in Quicksight

Question

Data is coming from Glue tables and this specific field is showing up in Quicksight as a string value.  I've tried every which way to change the data type of the field to date but then I lose all the data.  I've tried a calculated field but it skips 98% of the rows.

As an example, the datetime stamp in the string for this field shows as "2022-05-04 20:45:00+00".  I'm not sure what the +00 is and how to convert this field into a date field.

Please help.

Answer

Hello,

Seems your string format is not compatible with appropriate format of datetime. This is bit trick to achieve as desired format, otherwise the covert will be skipped for value.

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. Refer the below document to apply more specific to your format.

**https://docs.aws.amazon.com/quicksight/latest/user/parseDate-function.html**

Because "2019-08-28T18:00:53.031198Z" is in format of  "yyyy-MM-ddTHHmmss.SSSSSS" which has 3 more SSS unit than the limits of parsedate function, I have to use Left function to trim the string to first 21 digital first.

```
MyDate = "2019-08-28T18:00:53.031198Z" 
LeftFunction= left({MyDate},21) =  2019-08-28T180053.031
parseDateFunction = parseDate(LeftFunction,"yyyy-MM-dd'T'HHmmss.SSS") = 2019-08-28T18:00:53.031Z
```

You can also combine Left and ParseDate Function together by using below function to create a calculated field to convert your string date to date time format.

```
NewDate = parseDate(left({MyDate},21),"yyyy-MM-dd'T'HHmmss.SSS") 
```

Once your String field has converted to DateTime field, you can use formatDate function to modify the date format or adjust timezone.

**https://docs.aws.amazon.com/quicksight/latest/user/formatDate-function.html**

For example:

```
formatdate({NewDate },"MM/dd/yyyy")
```

Convert string to date format in Quicksight

相关内容