1 Reply Latest reply: Jun 13, 2011 9:02 AM by crj RSS

    Cast string in a text field as date in UK format on reports

    796572
      Hi All,

      I have UK locale setup for all my users, so all date / time fields display in dd/mm/yyyy hh:mm:ss format. However this one field is a text field and has a timestamp value in it in US format like 05/26/2011 16:31:00 and I am trying to display it like 26/05/2011 16:31:00 in reports.

      I tried to Cast it as DATE or TIME or TIMESTAMP it errors out saying invalid month. Understandable.

      The best I have managed to do is to simply use the SUBSTRING, LEFT and RIGHT functions as below and rearrange the characters to display it the way I want, but I don't want to hard code it that way. There are a few US locale users as well and it will impact them if I hard code it.

      SUBSTRING ("- Service Request Custom Attributes".TEXT_35 FROM 4 FOR 3) || LEFT("- Service Request Custom Attributes".TEXT_35, 3) || RIGHT("- Service Request Custom Attributes".TEXT_35, 13)

      Is there a way to achieve this? Please suggest. The reason I am doing it is in the same report there are other date / time fields displaying in UK format except this one so it's confusing to users.

      Regards,
      Udaya