CODEWITHSUNDEEP
oracle-databaseplsql
tom
tom

Reputation: 225

How to understand given sysdate is Date or Timestamp

I have a procedure.It takes date parameter with type Date.What i want to know is the other users send sysdate to my procedure.Which format they send sysdate to my procedure?

For example: 01/02/2021 or 01/02/2020 00:00:00(timestamp)

Does my procedure accepts all sending formats?Maybe Date type converts sending formats this style 01/02/2020.I am not sure.

I mean that does my Date parameter accepts all date formats because I want to use date in my procedure without seconds or minutes.

My procedure is

DECLARE
var_P_DATE                DATE;    (for example : 01/01/2021)
BEGIN
 SELECT last_day(var_P_DATE) INTO v_last_day FROM DUAL; (31/01/2021)
 if v_last_day = var_P_DATE (it returns false because no second or minutes)
  .....
END;

I used DBMS.OUTPUT. I think Date type converts just like this 01/02/2021 and i do not get any error but i am not sure.

Upvotes: 2

Views: 751

Answers (3)

Alex Poole
Alex Poole

Reputation: 191570

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

Upvotes: 3

Aleksej
Aleksej

Reputation: 22969

Date type in Oracle has hours, minutes and seconds, timestamp has fractions:

SQL> declare
  2     vDate date := sysdate;
  3     vTimeStamp timestamp := systimestamp;
  4  begin
  5     dbms_output.put_line('Date: ' || vDate);
  6     dbms_output.put_line('Timestamp: ' || vTimestamp);
  7  end;
  8  /
Date: 2021-02-18 09:49:32
Timestamp: 18-FEB-21 09.49.32.015953 AM

If you want to use just the date part, with no time, of a date variable, use something like trunc(vDate):

SQL> declare
  2     vDate date := sysdate;
  3     vTimeStamp timestamp := systimestamp;
  4  begin
  5     dbms_output.put_line('Date: ' || vDate);
  6     dbms_output.put_line('Date truncated: ' || trunc(vDate));
  7     dbms_output.put_line('Timestamp: ' || vTimestamp);
  8  end;
  9  /
Date: 2021-02-18 09:51:51
Date truncated: 2021-02-18 00:00:00
Timestamp: 18-FEB-21 09.51.51.024384 AM

An example of how comparison works on date variables:

SQL> declare
  2      vDate1  date;
  3      vDate2  date;
  4  begin
  5      vDate1 := sysdate;
  6      dbms_lock.sleep(5); /* wait 5 seconds */
  7      vDate2 := sysdate;
  8      --
  9      if vDate1 = vDate2 then
 10          dbms_output.put_line('Equal');
 11      else
 12          dbms_output.put_line('NOT equal');
 13      end if;
 14      --
 15      if trunc(vDate1) = trunc(vDate2) then
 16          dbms_output.put_line('Equal, truncated');
 17      else
 18          dbms_output.put_line('NOT equal, truncated');
 19      end if;
 20  end;
 21  /
NOT equal
Equal, truncated

Upvotes: 1

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65408

Just apply TRUNC() function and use that variable of type DATE within the procedure after defining the data type of var_P_DATE as TIMESTAMP

CREATE OR REPLACE PROCEDURE myproc( var_P_DATE TIMESTAMP) AS
 dt DATE := TRUNC(var_P_DATE) ;
BEGIN
   ...
   ...
END;
/

If you mean DBMS_OUTPUT.PUT_LINE by DBMS.OUTPUT, then that's completely irrelevant with your current conversion, that's just used to display result to the console as a string.

Upvotes: 1

Related Questions