Add th and nd in date

Q&A
, ,
1

concat(
formatDate(parseDate(concat(
substring(toString(sum(DATE2)), 1, 4), ‘-’,
substring(toString(sum(DATE2)), 5, 2), ‘-’,
substring(toString(sum(DATE2)), 7, 2)
), ‘yyyy-MM-dd’), ‘dd-MMM-yyyy’),
’ - ',
formatDate(parseDate(concat(
substring(toString(sum(DATE2)), 9, 4), ‘-’,
substring(toString(sum(DATE2)), 13, 2), ‘-’,
substring(toString(sum(DATE2)), 15, 2)
), ‘yyyy-MM-dd’), ‘dd-MMM-yyyy’)
),

through this i m getting date but i wand to add th , nd ,st in it like- 21st july 2022
22nd dec 2022

2

Hi @Siddhi_21

To include the “st,” “nd,” “rd,” or “th” suffix to the day in the date format, you can use a calculated field that handles this logic separately for the day of the month. Here’s how you can do it by splitting the logic for the suffix:

  1. Extract the day part from your date.
  2. Create a conditional statement to append the correct suffix.
  3. Concatenate it with the rest of your formatted date.

Example:

concat(
substring(toString(DATE2), 5, 2),
    ifelse(
        isNull(DATE2),'',
        substring(toString(DATE2), 5, 2) = '01' or substring(toString(DATE2), 5, 2) = '21' or substring(toString(DATE2), 5, 2) = '31', 'st',
        substring(toString(DATE2), 5, 2) = '02' or substring(toString(DATE2), 5, 2) = '22', 'nd',
        substring(toString(DATE2), 5, 2) = '03' or substring(toString(DATE2), 5, 2) = '23', 'rd', 
        'th'
    ),
' ',
substring(toString(formatDate(parseDate(concat(
substring(toString(DATE2), 1, 4), '-',
substring(toString(DATE2), 5, 2), '-',
substring(toString(DATE2), 7, 2)), 'yyyy-MM-dd'), 'MMM/dd/yyyy')),1,3),
' ',
substring(toString(DATE2), 1, 4),
' - ',
substring(toString(DATE2), 13, 2),
    ifelse(
        isNull(DATE2),'',
        substring(toString(DATE2), 13, 2) = '01' or substring(toString(DATE2), 13, 2) = '21' or substring(toString(DATE2), 13, 2) = '31', 'st',
        substring(toString(DATE2), 13, 2) = '02' or substring(toString(DATE2), 13, 2) = '22', 'nd',
        substring(toString(DATE2), 13, 2) = '03' or substring(toString(DATE2), 13, 2) = '23', 'rd', 
        'th'
    ),
' ',
substring(toString(formatDate(parseDate(concat(
substring(toString(DATE2), 9, 4), '-',
substring(toString(DATE2), 13, 2), '-',
substring(toString(DATE2), 15, 2)), 'yyyy-MM-dd'), 'MMM/dd/yyyy')),1,3),
' ',
substring(toString(DATE2), 9, 4)
)

image
image750×157 20.2 KB

Hope this helps!

1 Like
4

I WANT ONLY 2023 SHOULD BE LIKE 23 ONLY

5

hey @Xclipse can u plz help me with this and having problem to concate integer colum with sig colum nd % plz look into that also