Format Hour in DB2?

Question

Is there any way to display db2 Hour function with AM and PM?

select hour(TIMESTAMP) from ORDERS with ur 

This will give out like 5,6,7 etc..

But i want AM/PM after the Hour time.

I'd like to Dislpay as 5AM,6AM,7AM. Is it possible in db2?

Answer 1

Use the TIME() and CHAR() functions:

SELECT CHAR(TIME(timestamp), USA)
FROM Orders
WITH UR

Although, honestly, you should be doing this type of formatting in the application layer, not the SQL Layer.
(Statement run on my local DB2 instance)

---

EDIT:

Sorry, I missed that part earlier. Going through the documentation has shown me a new function, VARCHAR_FORMAT(). Assuming you're on DB2 9.5, the following should grant you some form of what you're looking for:

SELECT VARCHAR_FORMAT(timestamp, 'HH12 AM')
FROM Orders
WITH UR

Unfortunately, I can't test this myself, as iSeries V6R1 doesn't support the HH12 flag (HH24 only, what?). Otherwise, you're going to have to parse it out yourself.