Thursday, 24 November 2016

Ordering & Formatting Date Columns in APEX

Quite often I'll find I'd like to display a date with the time in a column within an APEX report, but you end up wondering how to control the wrap.

This is one problem that probably has a half dozen solutions, but I think this is the cleanest. And blogging about it helps me remember next time.

Depending on your screen size, you might be faced with something that takes up too much space per row.

not a good wrap

One solution is to turn that column into a formatted character string
,to_char(created_date,'DD-MON-YYYY"<br>"HH24:MI:SS') created_date
Note the HTML break tag surrounded by double quotes, this will put the time on the next line.

Combine this with styling on the column to stop wrapping.
Column setting CSS Style: white-space:nowrap
And you'll get a neater output.

If column is varchar, won't order nice

But there's a problem. And it's a deal breaker if you want to allow the user to order the column.
Your output will be ordered Apr, Aug, Dec, Feb, Jan... sense a trend? Alphabetical, not chronological.
Applying formatting within SQL is something you generally want to avoid.

Instead, just use the funky date format mask and apply it in a declarative manner within the column attributes, along with the CSS Style. This will allow the report to honour the appropriate ordering on the date columns, whatever format mask you require.

Declarative column attributes, using DD-MON-YYYY "<br>"HH24:MI:SS

Either way you need to set "Escape special characters" to No. I think your individual date values will be safe from cross site scripting.

These settings, along with the other Column Formatting options are used frequently in my applications, particularly HTML Expression. Just remember, if you use style frequently, define a class in your page/application/theme CSS definition and use that instead.
.date_fmt {white-space:nowrap;}


No comments: