Oracle sql convert timestamp into format

1
votes

I have a timestamp column 'ts'. I need to convert it into the format:

'DD-MON-YYYY hh24:mi'.

I used this to complete the requirement: 

to_char(ts, 'DD-MON-YYYY hh24:mi').

The problem is this returns a String. I am using Oracle Apex and outputting everything in a table. The table allows sorting. Now when I sort it sorts the column as a string and not as a date, so the sorting is not accurate. How can I convert a timestamp into the format I need while still being a timestamp/date field (any type as long as it accurately will sort by real dates).

2
sqloracleoracle-apex
By its very nature, a timestamp/date does not have a format. It's only when you convert it to a string that it has a format. so asking for your value to remain a timestamp or date while having a format doesn't actually make sense. I don't know apex, but isn't there a way for you to simply return a timestamp, and tell apex how to format them on display? - sstan
Another thought worth mentioning. Are you stuck with that specific date format? or can you pick a more standard one, like YYYY-MM-DD HH24:MI, which would give you proper date sorting, even while formatted? - sstan

2 Answers

1
votes

@sstan is right. There are four date formats that can be set at the workspace level, and at the application level: Application Date Format, Application Date Time Format, Application Timestamp Format, and Application Timestamp Time Zone Format. That way your timestamp stays a timestamp and so it sorts correctly. Select the application, click on the Edit Application Properties button (top rightish), click on the Globalization button (top leftish), and there you can set the date formats.

0
votes

Since you are already calling the table output the 

select to_char(ts,...) as formatteddate, original_date

columns but then in the sort do the following:

ORDER BY original_date