Oracle SYSDATE input data type

0
votes

Form the client I need to pass a SYSDATE argument to PL/SQL. In the server it need to be converted to date, for which iam using TO_DATE(in_timestamp, 'DD-MON-YYYY HH24:MI:SS'); What should be the data type of in_timestamp?

2
oracleplsql
:If you are passing SYSDATE to in_timestamp then datatype will be of DATE type .Are you returnig the same variable after converting it ? - Gaurav Soni
i will not be returning any value from procedure. - sach
:then go for date as mentioned by Erkan Haspulat in his answer - Gaurav Soni
Do you mean SYSDATE, the oracle pseudo-column, or just a system generated date? - APC
Naming something in_timestamp when you are looking for a data may be confusion. People may think you are asking for a timestamp instead of a date. - Shannon Severance

2 Answers

5
votes

SYSDATE is itself a date, and it seems like the target field is also a date (Since you used TO_DATE()). Thus you don't actually need a conversion here.

Just pass SYSDATE and use it in your PL/SQL block, meaning in_timestamp should be a date.

0
votes

As far as TO_DATE is concerned you can have CHAR, VARCHAR or VARCHAR2(recommended) basically it should be of String type as following examples suggest:-

to_date('2003/07/09', 'yyyy/mm/dd') would return a date value of July 9, 2003.
to_date('070903', 'MMDDYY') would return a date value of July 9, 2003.
to_date('20020315', 'yyyymmdd') would return a date value of Mar 15, 2002.

You can find more information related to TO_DATE at this link,

EDIT

*"However, if you are passing sysdate you dont't need to use TO_DATE again because it is already a Date value..."*as mentioned by @Gaurav and Hence the dataType of in_timestamp should be DATE..