Syntax for to_date function using sysdate

[kramerd1506]

Hello all,

I need the syntax for to_date(trunc(sysdate) at 9:00am). Nothing I try seems to work. Thanks in advance.

 

[SantaMufasa]

Kramer, the code you want is:

to_date(trunc(sysdate)||'09:00:00','dd-mon-yyhh24:mi:ss')

The proof of concept is:

select to_char(to_date(trunc(sysdate)||
   '09:00:00','dd-mon-yyhh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')
from dual;

TO_CHAR(TO_DATE(TRU
-------------------
2005-06-07 09:00:00

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[sem]

I suppose that depending on current NLS_DATE_FORMAT value trunc(sysdate)||'09:00:00' may return something like '08.06.0509:00:00' or even '08.06.05 00:00:0009:00:00' that obviously can not be converted to date.
I suggest another solution:

to_date(to_char(sysdate,'dd.mm.yyyy')||'09:00:00', 'dd-mm-yyyyhh24:mi:ss')

Regards, Dima

http://seminihin.narod.ru

 

[lewisp]

Another solution to add to Dave and Dimas excellent solutions, perhaps:

Trunc(SYSDATE) + (9/24)

will result in a proper date.