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("Time_Stamp", adDate, adParamInput, , SYSDATE)

Thanks!
marcmestAsked:
Who is Participating?
 
mdouganConnect With a Mentor Commented:
I've created a VB function to get the SysDate from Oracle:

Function SysDate() As Variant
'*************************************************************************************************************************
'   Retrieves the current date/time from the database server
'   Example call:
'       SQL = SQL + "SET LASTUPDATED = " & ToDate(SysDate())
'*************************************************************************************************************************
Dim sErrors As String
Dim sSQL As String
Dim vReturn As Variant
Dim lrs      As Recordset

    On Error GoTo SysDateErr
   
    SysDate = ""
    vReturn = ""
   
    sSQL = "SELECT SYSDATE AS SERVERDATE FROM DUAL"
   
    Set lrs = New ADODB.Recordset
    lrs.CacheSize = 100
    CN.CursorLocation = adUseServer
       
    lrs.Open sSQL, CN, adOpenForwardOnly, adLockReadOnly, adCmdText
   
    If Not lrs.BOF And Not lrs.EOF Then
        If Not IsNull(lrs("SERVERDATE")) Then
            If IsDate(lrs("SERVERDATE")) Then
                vReturn = Format(lrs("SERVERDATE"), "m/d/yyyy hh:nn:ss AM/PM")
            End If
        End If
    End If

    SysDate = vReturn
    lrs.Close
    Set lrs = Nothing
   
SysDateExit:
    Exit Function
SysDateErr:
    Screen.MousePointer = 0
    sErrors = "SysDate " & Err.Description
    frmMessage.MessageBox sErrors, vbCritical, Err & ""
    Resume SysDateExit

End Function

So, you're function call would be:

Set testParm = testCmd.CreateParameter("Time_Stamp", adDate, adParamInput, , SYSDATE())
0
 
eprettiCommented:
There is no way to do that in this way .. because sysdate is a Oracle function, if you want to set the sysdate there what you have to do is, in the SQL put this, for ex.

"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 :-)
0
 
eprettiCommented:
Sorry, the Sql is
"INSERT INTO Table_Name Values (?, ?, SYSDATE)"

Why do you say it is wrong? What are your trying to do?
0
 
marcmestAuthor Commented:
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...
0
 
mdouganCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.