Brreaking down date format in quicksight using calculated field

Question & Answer
1

I have date filed format 2021-09-06 22:00:00, I want to create calculate filed that separates Date (2021-09-06) and time (22:00:00) in two separate columns. Any help would be appreciated.

2

Do you have date field defined as Date or String?

If it is defined as Date

  • StringDate: formatDate({date}, ‘yyyy-MM-dd’)
  • StringTime: concat(toString(extract(“HH”, {date})), “-”, toString(extract(“MI”, {date})), “-”, toString(extract(“SS”, {date})))

If the field is stored as String

  • StringDate: left({date}, locate({date}, " ")-1)
  • StringTime: right({date}, strlen({date})-locate({date}, " "))
1 Like
3

Date field is defined as Date

It still gave me error for both: “The syntax of the calculated field expression is incorrect. Correct the syntax and choose Create again.” any suggestions?

4

Sorry, it worked when I typed in all by my self instead of copy paste… However one of the flows I saw was, the output was in text not date format. Any suggestion to convert that into date?

5

You can use parseDate() function to convert the string back to date: parseDate - Amazon QuickSight
image