Format datetime field in 'dd-MMM-yyyy hh:mm:ss a'

Q&A
, , ,
1

Hello Team,

I have datetime field with 24 hour format . I want to format that field to 12 hour format with am pm .
Example I have to show date field = 12-Jul-2025 01:10:10 AM
I am getting error while using formatDate function in calculated field . Any idea how to get this format .

ifelse(

${12hourformat}=‘Yes’ ,

formatDate({local_start_at},‘dd-MMM-yyyy h:m:s a’),

formatDate({local_start_at},‘dd-MMM-yyyy HH:mm:ss’)

)

2

Hello Rohit,

To format a datetime field in Quick Sight to 12-hour format with AM/PM, you’may need to modify your formatDate function slightly. Please try the syntax below:

ifelse(
    ${12hourformat}='Yes',
    formatDate({local_start_at}, 'dd-MMM-yyyy hh:mm:ss a'),
    formatDate({local_start_at}, 'dd-MMM-yyyy HH:mm:ss')
)

Key changes to note:

  1. Use hh instead of h for hours in 12-hour format (adds leading zero)
  2. Use mm instead of m for minutes (adds leading zero)
  3. The a marker at the end will show AM/PM

Hope this helps.
Cheers,
Deep

3

Getting below error . I am using table visual

image
image606×303 8.02 KB

4

What is your underlying date format, could you share that ?

Thanks,
Deep

5

Hi @Rohit_Nikam

I think there are nuances related to formatdate function as mentioned in the documentation

Your datasource has a role to play. For example I tried the 12 hour format on a datetime field for my mySQL datasource and fails with the same error as you get.

image
image552×245 18.9 KB

Regards,
Giri

1 Like
6

Hi @Deep ,

I am using direct redshift query in dataset . Following is the field in dataset

image
image227×250 5.67 KB

7

HEllo Rohit,

To convert the ISO 8601 date format (example - > 2024-08-15T03:00:59.00Z) to 12-hour format in Amazon Quick Sight, you can use the parseDate() function in a calculated field. See here for referecne

For timestamps with ‘T’ and ‘Z’ characters like yours, you need to handle these special characters by enclosing them in single apostrophes.
You can create a calculated field using this formula:

parseDate({YourDateField}, 'yyyy-MM-dd\'T\'HH:mm:ss.SS\'Z\'')

After parsing the date correctly, you can format it to display in 12-hour format using the formatDate() function with the appropriate tokens. Reference

To get 12-hour format, use the ‘h’ or ‘hh’ token for hours and ‘a’ for am/pm indicator.

For example:

formatDate(parseDate({YourDateField}, 'yyyy-MM-dd\'T\'HH:mm:ss.SS\'Z\''), 'yyyy-MM-dd hh:mm:ss a')

Hope this helps.
Cheers,
Deep

8

It is still giving error

image
image1920×1080 173 KB

9

Hello @Rohit_Nikam

Were you able to find a solution or are you still working on this?

I do think your primary issue is related to what @Giridhar.Prabhu mentioned above.

10

Hi @duncan ,

For now I have broke down the date in multiple parts and then concatinated them with if else statement .

Do consider this as an feature