marcmest
asked on
Passing Oracle's SYSDATE using ADO's CreateParameter
Does anyone know how to pass Oracle's SYSDATE as an input parameter to a stored procedure, using ADO's CreateParameter statement?
Example:
Set testParm = testCmd.CreateParameter("T
Thanks!
Example:
Set testParm = testCmd.CreateParameter("T
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Sorry, the Sql is
"INSERT INTO Table_Name Values (?, ?, SYSDATE)"
Why do you say it is wrong? What are your trying to do?
"INSERT INTO Table_Name Values (?, ?, SYSDATE)"
Why do you say it is wrong? What are your trying to do?
ASKER
Many months later...
Another way I thought of to accomplish this is to pass either Null or some bogus date like 1/1/1900 to indicate to the stored procedure that Sysdate should be used.
Then in the stored procedure, check for these values and use SYSDATE as a substitute value using NVL or DECODE when appropriate...
May need to pass dates in as Varchar2 instead of date to accomplish some of this...
Another way I thought of to accomplish this is to pass either Null or some bogus date like 1/1/1900 to indicate to the stored procedure that Sysdate should be used.
Then in the stored procedure, check for these values and use SYSDATE as a substitute value using NVL or DECODE when appropriate...
May need to pass dates in as Varchar2 instead of date to accomplish some of this...
I guess the question is, will the date parameter sometimes not be the SYSDATE? If it will always be the SYSDATE then the stored procedure shouldn't need to have this passed in as a parameter, you'd just include it in your select or update statement.
Another thought though, some guys on my latest SQL Server project have been talking about using Optional parameters to the Stored Proc. I don't know if Oracle supports optional parameters or not, but you might check to see if it does. If so, then you might be able to define SYSDATE as the default. Something like:
Create Procedure MyProc(
@UserID varchar(10),
@HireDate datetime default SYSDATE)
AS
Select * from Users
Where UserID = @UserID
And HireDate >= @HireDate
Another thought though, some guys on my latest SQL Server project have been talking about using Optional parameters to the Stored Proc. I don't know if Oracle supports optional parameters or not, but you might check to see if it does. If so, then you might be able to define SYSDATE as the default. Something like:
Create Procedure MyProc(
@UserID varchar(10),
@HireDate datetime default SYSDATE)
AS
Select * from Users
Where UserID = @UserID
And HireDate >= @HireDate
"INSERT INTO [Table Name] Values (?, ?, SYSDATE())"
add to the command the parameters that you need and put the function in the SQL, not in a parameter. Bye :-)