- Newest
- Most votes
- Most comments
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")
Relevant content
- Accepted Answerasked a year ago
- AWS OFFICIALUpdated 24 days ago