• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 4707
  • Last Modified:

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
0
Talitha30
Asked:
Talitha30
  • 3
  • 2
  • 2
3 Solutions
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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:
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
 
sdstuberCommented:
why the B?  especially if the answer worked "perfectly"  ?
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now