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.
The reason you're receiving the "Invalid Month" error is that the date format expected by the cast function is dd-mmm-yyyy (as in: 11-Jun-2011). You can use substring and case statements to pull out what you need from the data field, and then cast it to an actual date.