?
Solved

How to truncate seconds in datetime or timestamp in Oracle

Posted on 2012-04-05
9
Medium Priority
?
3,923 Views
Last Modified: 2012-04-29
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
Comment
Question by:Talitha30
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
9 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1440 total points
ID: 37812531
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
 

Author Comment

by:Talitha30
ID: 37812538
yes it's a varchar datatype
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1440 total points
ID: 37812566
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37812576
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
 

Author Comment

by:Talitha30
ID: 37812636
That's true.....
Your solution worked perfectly!
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 560 total points
ID: 37812950
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 37883470
why the B?  especially if the answer worked "perfectly"  ?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question