CODEWITHSUNDEEP
sqloracle-databasesubqueryora-00936
shary.sharath
shary.sharath

Reputation: 709

How to pass Timestamp to Oracle Function?

I have a table and storing date value in it and I need to fetch that value and pass that as parameter to function. But whenever I try to execute my code it's throwing error like 

ORA-00936: missing expression.

Following is my code sample:

SELECT refno, 
       Tatcalculate(to_timestamp(SELECT h_modified_date
                                   FROM TPADETAILS
                                  WHERE refno = 'WOC0021946','DD/MM/YYYY HH24:MI:SS'))
 FROM Table1;

Table1 is my table and h_modified_date is the column. Tatcalculate() is the function.

When I run inner query it's returning the date value from the table but when I execute complete above query then its throwing the error.
Anybody please help me to resolve this.

Upvotes: 0

Views: 4158

Answers (3)

Sandeep
Sandeep

Reputation: 806

Try this.....

SELECT refno, Tatcalculate(to_timestamp((SELECT  to_char(h_modified_date,'DD/MM/YYYY HH24:MI:SS') FROM TPADETAILS WHERE refno='WOC0021946'),'DD/MM/YYYY HH24:MI:SS'))
FROM Table1;

Upvotes: 0

Boneist
Boneist

Reputation: 23588

I would put the to_timestamp() around the column name, rather than around the select statement.

Also, you need to enclose the select statement in its own brackets. Thus:

SELECT refno, Tatcalculate((SELECT to_timestamp(h_modified_date,'DD/MM/YYYY HH24:MI:SS') FROM TPADETAILS WHERE refno='WOC0021946'))
FROM Table1;

ETA: Given the extra information that the tatcalculate parameter is DATE datatype and also the h_modified_date column is a DATE, there is absolutely no need to do any datatype conversions at all. Therefore, the query becomes:

SELECT refno, Tatcalculate((SELECT h_modified_date FROM TPADETAILS WHERE refno='WOC0021946'))
FROM Table1;

Upvotes: 1

BornToCode
BornToCode

Reputation: 217

give column name

SELECT refno, Tatcalculate(to_timestamp(SELECT h_modified_date FROM TPADETAILS WHERE refno='WOC0021946','DD/MM/YYYY HH24:MI:SS')) as TAT FROM Table1;

Upvotes: 0

Related Questions