How to get a date at midnight

Question

I would like to know how to get a date with 00 hour, 00 minutes and 00 seconds.

This

select to_char(sysdate, 'dd/MM/YYYY HH:mm:ss') from dual;

gives the date at the time I asked it

If I don't give any hour :

select to_char(to_date('03/05/2017', 'DD/MM/YYYY'), 'DD/MM/YYYY HH:mm:ss') from dual;

I have a date at noon.

How can I get a date (with sysdate or giving my own date with to_date) at 00:00:00

Thank you

Answer 1

Use TRUNC( date_value, format_model ) and either omit the format model (the default is to truncate to midnight) or use one of the format models 'ddd', 'dd' or 'j':

SELECT TRUNC( SYSDATE ) FROM DUAL;

I dont give any hour :

select to_char(to_date('03/05/2017', 'DD/MM/YYYY'), 'DD/MM/YYYY HH:MI:SS') from dual;

I have a date at noon.

No, you have the date at midnight formatted with a 12-hour clock.

select to_char( to_date('03/05/2017', 'DD/MM/YYYY'), 'DD/MM/YYYY HH:MI:SS PM')

Outputs 03/05/2017 12:00:00 AM

To get a 24-hour clock you need to use HH24 in the format model (rather than HH or HH12 which is a 12-hour clock):

select to_char( to_date('03/05/2017', 'DD/MM/YYYY'), 'DD/MM/YYYY HH24:MI:SS')

Outputs 03/05/2017 00:00:00