CODEWITHSUNDEEP
oracle-databasedate
mlwacosmos
mlwacosmos

Reputation: 4561

How to get a date at midnight

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

Upvotes: 5

Views: 22763

Answers (2)

MT0
MT0

Reputation: 168623

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

Upvotes: 11

Will Wagner
Will Wagner

Reputation: 1

mm should be replaced with mi for minutes:

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

Upvotes: 0

Related Questions