0

Sysdate returns the current system date and time and its return type is of date. But shouldn't date type only contain information on date and not on time. Why is this acceptable?

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
John
  • 13
  • 3
  • 1
    Oracle datatype `DATE` is a bit misleading. In fact it always contains date + time value. If like to skip the time part use `TRUNC(SYSDATE)`. However, it still has time value which is just set to `00:00:00` – Wernfried Domscheit Jan 13 '22 at 10:04
  • 2
    [The documentation for the `DATE` data type](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6) says "This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND". Which part do you find unacceptable; that `DATE` includes the time (which was a very early decision, but misleading/confusing), or that `SYSDATE` returns a `DATE` (which seems reasonable), or that the date it returns includes the time? (`TIMESTAMP` came later, and it was too late to then change `DATE` to not include the time...) – Alex Poole Jan 13 '22 at 10:35

1 Answers1

6

Looking back:

  • Oracle version 2 was released in 1979.
  • The American National Standards Institute adopted SQL as a standard in 1986.
  • ISO 9075: "Information Technology Database Languages - SQL" was adopted in 1987.
  • Oracle version 6 was released in 1988.

Therefore, Oracle's implementation of a DATE data type predates the ANSI standard and Oracle chose to keep their original implementation rather than to break the backwards compatibility of the database versions to implement the ANSI/ISO standard.

But shouldn't date type only contain information on date and not on time.

The ANSI/ISO standard is for a DATE to contain year, month and day components.

Oracle's implementation predates the ANSI/ISO standard and does not comply with it for historic reasons. It is a binary data type that consists of 7 bytes for century, year-of-century, month, day, hour, minute and second. It ALWAYS has those components.

MT0
  • 143,790
  • 11
  • 59
  • 117