Oracle SYSDATE input data type

Question

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?

: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
– Gaurav Soni, Commented Apr 9, 2012 at 10:04
:then go for date as mentioned by Erkan Haspulat in his answer — Gaurav Soni
– Gaurav Soni, Commented Apr 9, 2012 at 10:17
Do you mean SYSDATE, the oracle pseudo-column, or just a system generated date? — APC
– APC, Commented Apr 9, 2012 at 15:05
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
– Shannon Severance, Commented Apr 9, 2012 at 17:18

Erkan Haspulat · Accepted Answer · 2012-04-09 10:09:37Z

5

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.

answered Apr 9, 2012 at 10:09

Erkan Haspulat

12.7k7 gold badges43 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Amit · Accepted Answer · 2012-04-09 10:50:45Z

0

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..

edited Apr 9, 2012 at 10:50

answered Apr 9, 2012 at 10:03

Amit

13.5k18 gold badges84 silver badges150 bronze badges

1 Comment

Gaurav Soni Over a year ago

:If he's passing sysdate then why he should be recommmended to store that in varchar*?,

Collectives™ on Stack Overflow

Oracle SYSDATE input data type

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related