CODEWITHSUNDEEP
sqloracle-databasedatesqldatatypesto-char
Adriana
Adriana

Reputation: 11

Date data type in Oracle, 12 pm

How do I know if the hour 00:00:00 in a date field means 12 pm or means nothing, null, without looking for the data source of this table?

FORMAT DD/MM/YYYY HH24:MI:SS

DATE_WITHOUT_HOUR: 03/01/2022 00:00:00

DATE_WITH_HOUR: 04/01/2022 21:30:00

FORMAT DD/MM/YYYY HH:MI:SS

DATE_WITHOUT_HOUR: 03/01/2022 12:00:00

DATE_WITH_HOUR: 04/01/2022 09:30:00

The previous date was a result of a insert of two values: ('03/01/2022', '04/01/2022 21:30:00')

After that insert how anyone knows the hour of the first date is meaningless without checking out the insert?

Thanks!

Upvotes: 1

Views: 5245

Answers (2)

MT0
MT0

Reputation: 168232

In Oracle, a DATE is a binary data type consisting of 7 bytes that represent century, year-of-century, month, date, hour, minute and second. It ALWAYS has those components and it is NEVER stored with any particular formatting.

You cannot have a DATE without an hour.

Note: The client application you use to talk to the database may chose to implicitly apply a format to binary date values it receives to make those values more meaningful to you, the user; however, this is a function of the client application you are using and is not something done by the database.

If you want to display a DATE then you can use TO_CHAR then:

  • The date format DD/MM/YYYY HH24:MI:SS is unambiguous as the hours will be displayed using the 24-hour clock.
  • The date format DD/MM/YYYY HH12:MI:SS AM (and HH is the same as HH12) is also unambiguous as, although the hours will be displayed using a 12-hour clock, a meridian indicator is shown that disambiguates between AM and PM.
  • The date format DD/MM/YYYY HH12:MI:SS is ambiguous and there is no way to differentiate between AM and PM. You should not use this format model without the meridian indicator.

Upvotes: 3

Littlefoot
Littlefoot

Reputation: 142958

Oracle stores dates in its internal format. It is up to you to take care about the way you store values into DATE datatype columns. If you stored it as

to_date('2022-01-31 20:52', 'yyyy-mm-dd hh24:mi')

then that's the value.

While retrieving value from the table, you have different options, but - any you use will result in the same value (it just might be differently displayed):

SQL> create table t_date (col date);

Table created.

SQL> insert into t_date values (to_date('2022-01-31 20:52', 'yyyy-mm-dd hh24:mi'));

1 row created.

Default NLS settings in my database don't even display 4 digits for years; time component isn't visible:

SQL> select * from t_date;

COL
--------
31.01.22

TO_CHAR with 24-hours time format:

SQL> select to_char(col, 'dd.mm.yyyy hh24:mi:ss') result from t_date;

RESULT
-------------------
31.01.2022 20:52:00

TO_CHAR with AM/PM:

SQL> select to_char(col, 'dd/mm/yyyy hh:mi am') result from t_date;

RESULT
-------------------
31/01/2022 08:52 PM

Altering session's date format mask:

SQL> alter session set nls_date_format = 'dd mon yyyy, hh24:mi';

Session altered.

SQL> select * from t_date;

COL
------------------
31 jan 2022, 20:52

SQL>

Whatever I did, that value remains the same. Therefore, it is really up to you.

Upvotes: 1

Related Questions