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

Thanks
Talitha30Asked:
Who is Participating?
 
sdstuberCommented:
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
0
 
slightwv (䄆 Netminder) 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.
0
 
Talitha30Author Commented:
yes it's a varchar datatype
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) 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;
0
 
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?
0
 
Talitha30Author Commented:
That's true.....
Your solution worked perfectly!
0
 
sdstuberCommented:
why the B?  especially if the answer worked "perfectly"  ?
0
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.