Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Stuti.

Asked: September 09, 2016 - 4:04 pm UTC

Last updated: September 10, 2016 - 8:39 am UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hello Again

Need your help..

How can convert a date to YYYY-MM-DD hh24:00:00 format

I have date like 2016-09-01 09:33:23 i want to have the output like 2016-09-01 08:00:00 (want to subtract and hour from the time and this format)

I want to know if there is some simple way to do?

Regards
Stuti

and Chris said...

Do you really want to convert 9:33:23 -> 8:00:00? Or should that read 9:00:00?

If it's the latter, that's easy! You can trim a datetime to a particular unit by passing the relevant format to trunc():

with dt as (
select to_date('2016-09-01 09:33:23', 'yyyy-mm-dd hh24:mi:ss') dt
from   dual
)
  select trunc(dt, 'mm') mth,
         trunc(dt, 'hh24') hrs,
         trunc(dt, 'mi') mins
  from   dt;

MTH                   HRS                   MINS                  
01-SEP-2016 00:00:00  01-SEP-2016 09:00:00  01-SEP-2016 09:33:00


http://docs.oracle.com/database/121/SQLRF/functions236.htm#SQLRF06151

If it's the former, then you'll have to explain in more detail why you go from nine thirty three to eight!

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Followup

Stuti Aggarwal, September 09, 2016 - 5:02 pm UTC

I would need the first part.. my requirement is that for the cuurent_timestamp i want to go 1 hr back trunc to hr part only..dont need mins
Connor McDonald
September 10, 2016 - 8:39 am UTC

In that case, it is simply

[yourdate]-1/24

and then the same functions Chris described before.

Cheers,
Connor

Thanks

Stuti, September 10, 2016 - 10:04 am UTC

I got that..