Oracle: Similar to Sysdate But Returning Only Time and Only Date

Oracle: Similar to sysdate but returning only time and only date

There is no such thing as a DATE only column in Oracle. The DATE datatype stores date and time.

If you only care about the date, you can:

INSERT INTO tbl (dtCol) VALUES (TO_DATE('20110929','YYYYMMDD');

This leaves the time component at 00:00:00. You don't have to display it though.

If you're only interested in the time component, you still have a date stored in the column. You'll just have to handle that on output. For example:

SQL> CREATE TABLE dt (d DATE);

SQL> INSERT INTO dt VALUES (TO_DATE('1:164800','J:HH24MISS'));

1 row inserted

Showing the actual contents of the column reveals a date was inserted:

SQL> SELECT * FROM dt;

D
--------------------
0/0/0000 4:48:00 PM

Selecting only the time component from the column gives you the output you want:

SQL> SELECT TO_CHAR(d, 'HH24:MI:SS') d FROM dt;

D
--------
16:48:00

SQL> 

If you think you need only a time column, you'll want to make sure you always insert the same date component.

select sysdate from dual returns only date, not time

Change your NLS settings: Tools -> Preferences -> Database -> NLS

Changing Default Date Format settings

Oracle current_date or sysdate without hours, minutes, seconds

Maybe you should try

trunc(sysdate)

function.

Different CURRENT_TIMESTAMP and SYSDATE in oracle

CURRENT_DATE and CURRENT_TIMESTAMP return the current date and time in the session time zone.

SYSDATE and SYSTIMESTAMP return the system date and time - that is, of the system on which the database resides.

If your client session isn't in the same timezone as the server the database is on (or says it isn't anyway, via your NLS settings), mixing the SYS* and CURRENT_* functions will return different values. They are all correct, they just represent different things. It looks like your server is (or thinks it is) in a +4:00 timezone, while your client session is in a +4:30 timezone.

You might also see small differences in the time if the clocks aren't synchronised, which doesn't seem to be an issue here.

SQL Developer is returning only the date, not the time. How do I fix this?

Can you try this?

Go to Tools> Preferences > Database > NLS and set the Date Format as MM/DD/YYYY HH24:MI:SS

Get only date without time in Oracle

Usually one would simply truncate the datetime with TRUNC:

TRUNC(pa.fromdate)

This removes the time part from the datetime, so you get the mere date. Then in your application layer, you would care about how to display it.

For example you have a GUI with a grid. The grid displays the dates according to the user's system settings (e.g. Windows region settings), but the grid knows it's dates and can sort accordingly. For this to happen, you'd fill the grid with dates, not with strings representing a date.

If you want a fixed string format (e.g. in order to write into a file), you can use TO_CHAR instead:

TO_CHAR(pa.fromdate, 'dd.mm.yyyy')

How to understand given sysdate is Date or Timestamp

Your procedure will only ever receive a date, because that is the data type of the formal parameter. When the procedure is called the caller can supply a date, or something that can be implicitly converted to a date (though they shouldn't; implicit conversions are generally a bad thing, particularly from strings).

The date data type includes time components. If you are being passed a date with a non-midnight time that you want to ignore, such as sysdate, you can use the trunc() function, with it's default 'DD' format; and you don't need to select from dual:

v_last_day := last_day(trunc(var_P_DATE));

If the caller passes in systimestamp then that will still be implcitly converted to a date by the time you see it - which means it loses any fractional seconds and time zone information, but retains hours, minutes and seconds.

Dates and timestamps do not have have any inherent human-readable format. A date can be displayed using various formats - see the documentation - either explicitly with to_char() and a format model, or implicitly using your session settings.

When you do

dbms_output.put_line(var_P_DATE);

you are doing an implicit conversion of the date value to a string, using the session's NLS_DATE_FORMAT setting. So, different users might see that in different formats. You have no control over that. If you want to see a specific format then specify that, e.g.:

dbms_output.put_line(to_char(var_P_DATE, 'YYYY-MM-DD'));

You also have no control over whether the caller sees that output - it's down to the application/client and its settings. It looks like you are probably only using it for debugging the comparison issue though, so that probably doesn't matter here.

So as a demonstration:

declare
  var_P_DATE date := sysdate;
  v_last_day date;
begin
  v_last_day := last_day(var_P_DATE);
  dbms_output.put_line(to_char(v_last_day, 'YYYY-MM-DD HH24:MI:SS'));
  v_last_day := last_day(trunc(var_P_DATE));
  dbms_output.put_line(to_char(v_last_day, 'YYYY-MM-DD HH24:MI:SS'));
end;
/

2021-02-28 09:59:02
2021-02-28 00:00:00

db<>fiddle demo

Why is the return type of sysdate DATE?

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.

---

Related Topics