CODEWITHSUNDEEP
sqldatabaseoracle-database
sria
sria

Reputation: 35

Extract hour from timestamp in 12 hour format with AM/PM indicator oracle

I'm trying to extract hour from time-stamp column (oracle) in 12 hour format with A.M/P.M indicator.

The column "subdate" has value 22-APR-20 04.18.40.000000000 PM

The Extract(HOUR from subdate) function returns

16

However, I want the value to be 4.00 PM

I have tried various options with the trunc and to_char functions but none of them gave the desired output.

Can you please help with this!

Thank you!

Upvotes: 0

Views: 4314

Answers (1)

user5683823
user5683823

Reputation:

Since you are talking about formats, you are not simply "extracting the hour" - you are really extracting a string.

TO_CHAR(subdate, 'hh.mi AM')

should do the trick.

ADDED: Based on comments. If you want : separator instead of . that is easy, just change it in the format mask. If you want to round down to the hour use trunc(subdate, 'hh') instead of subdate. If you need to drop the leading zero, you can wrap the entire expression in LTRIM(... , '0') or, as Alex Poole suggest, use the fm toggle like this: 'fmhh:fmmi AM'

Upvotes: 1

Related Questions