concatenating sysdate with fixed time in oracle

Question

I want to concatenate sysdate with my own time. Below is the query i have tried, but I am getting year as 0016 instead of 2016.

Are there any other ways to get the result like below?

Query:

select to_date(sysdate || ' 02:50:00','dd-mon-yyyy hh24:mi:ss') as MyTime from dual

Output:

MYTIME

3/12/0016 02:50:00 AM

Answer 1

One way

1. Convert SYSDATE to a string
2. Append your fixed time element
3. Convert back to a date

Put it altogether like this:

to_date(to_char(sysdate,'YYYY-MM-DD')||' 02:50:00','YYYY-MM-DD HH24:MI:SS' ) 

Alternative way: use arithmetic.

1. Strip the real time from SYSDATE
2. Add the number of seconds ((60*60*2)+(50*60)/(60*60*24)

Include your workings or not:

trunc(sysdate) + ( 10200 / 86400)

There is a third way: use an INTERVAL (basically a variant of the second way).

SQL> select trunc(sysdate) + to_dsinterval('0 02:50:00') as mytime
  2  from dual
  3  /

MYTIME
------
2016-03-12 02:50:00

SQL>