How to truncate seconds in datetime or timestamp in Oracle

The following query below only displays date of datetime field

Select Trunc(To_timestamp(adstime, 'yyyy-mm-dd hh24:mi:ss'), 'MI') time from hp_datetime;;

I only want to show 08:25 instead of 08:25:10

Who is Participating?

Improve company productivity with a Business Account.Sign Up

sdstuberConnect With a Mentor Commented:
don't even need a regexp

substr(adstime,12,5)   -- to get just the hours:minutes

substr(adstime,1,16)   -- to get the date with hours:minutes
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Is adstime a varchar2?

To remove the seconds, use to_char to turn it back into a string.  Just use the same format mask without the seconds.
Talitha30Author Commented:
yes it's a varchar datatype
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

slightwv (䄆 Netminder)Connect With a Mentor Commented:
Ok then, just wrap a to_char around it

Select to_char(Trunc(To_timestamp(adstime, 'yyyy-mm-dd hh24:mi:ss'), 'MI') ,  'yyyy-mm-dd hh24:mi') time from hp_datetime;
slightwv (䄆 Netminder) Commented:
Come to think of it, wouldn't a regex_replace be better to manipulate the varchar2 to truncate the minutes and remove the seconds?
Talitha30Author Commented:
That's true.....
Your solution worked perfectly!
why the B?  especially if the answer worked "perfectly"  ?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.