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

Accepted Answer

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`

How to get a date at midnight

Solution

Problem