?
Solved

Oracle Bind Variables - losing time component of date field on insertion

Posted on 2003-03-27
11
Medium Priority
?
2,496 Views
Last Modified: 2013-12-25
I need to use bind variables to insert a date-time into an Oracle DB column of type date.

The problem is that the time portion of the date gets truncated in Oracle.  I know the SQL to insert the date-time without bind variables.

The following is an example of the code used

Any ideas are appreciated.

Thanks,

Tim

 

Public Function insertRecord(ByVal id As String, _
                             ByVal type As String, _
                             ByVal dateTime As Date, _
                             ByVal pageNumber As Integer, _
                             ByVal FilePath As String) As Boolean
    Dim sqlCommand As String
    Dim dateString As String
    Dim record As ADODB.Recordset
    Dim returnVal As Boolean
    Dim vParam(4) As Variant
    Dim oCmd As ADODB.Command
   
    ' dateTime = Date
    On Error GoTo insertRecord_EH
                                     
    ' make sure db is open
    If (openDBConnection()) Then
           
        vParam(0) = CLng(id)
        vParam(1) = type
        vParam(2) = dateTime
        vParam(3) = pageNumber
        vParam(4) = FilePath
       
        ' set up SQL command
        sqlCommand = "insert into table values(?, ?, ?, ?, ?)"
                 
        Set oCmd = New ADODB.Command
        Set oCmd.ActiveConnection = g_cn ' g_cn is a global connection variable
        oCmd.CommandText = sqlCommand
        Set record = New ADODB.Recordset
        Set record = oCmd.Execute(, vParam)
      end if
      exit function

insertRecordIntoPagesTable_EH:
 End Function
0
Comment
Question by:joex
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8222118
Change this:
 vParam(2) = dateTime
To:
 vParam(2) = Format(dateTime, "dd-mmm-yyyy hh:mm:ss"

Anthony
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8222133
Missing a paranthesis, should be:
vParam(2) = Format(dateTime, "dd-mmm-yyyy hh:mm:ss")

Anthony
0
 

Author Comment

by:joex
ID: 8224414
Thanks, I tried this already and get the following message:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01830: date format picture ends before converting entire input string"

Is there a NLS_DATE_FORMAT or something like this that needs to be set to establish a default date format?

The following also does not work:

"insert into table values(?, ?, " + _
  "to_date('?', 'yyyy-mm-dd hh24:mi:ss'), " + _
  "?, ?, ?, ? )"

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:joex
ID: 8224748
Thanks, I tried this already and get the following message:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01830: date format picture ends before converting entire input string"

Is there a NLS_DATE_FORMAT or something like this that needs to be set to establish a default date format?

The following also does not work:

"insert into table values(?, ?, " + _
  "to_date('?', 'yyyy-mm-dd hh24:mi:ss'), " + _
  "?, ?, ?, ? )"

0
 

Author Comment

by:joex
ID: 8224754
I think I solved this problem myself.

To set up NLS_DATE_FORMAT, do the following:

Start->Run regedit.exe (set "Run in separate memory space")
Expand to HKEY_LOCAL_MACHINE->SOFTWARE->ORACLE.
Select ORACLE in left pane.
In right pane, check to see if an entry already exists for NLS_DATE_FORMAT.
If no entry exists:
  Right click over ORACLE in left pane.
  Select New->String Value.
  Type in NLS_DATE_FORMAT
End if
In right pane, right click over NLS_DATE_FORMAT.
Select Modify.
Enter DD-MON-YYYY (or your favorite Y2K-compliant date format string) in the Value box (note do not use any quotes), and hit Enter.
0
 

Author Comment

by:joex
ID: 8224763
If someone has an SQL-only solution to this, I will give them points
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 8225319
See if this makes any difference:
Dim sDate As String
sDate = Format(dateTime, "yyyy-mm-dd hh:mm:ss")
vParam(2) = "{ts '" & sDate & "')"

Or this using Oracle's to_date function:
Dim sDate As String
sDate = Format(dateTime, "yyyy-mm-dd hh:mm:ss")
vParam(2) = "to_date('" & sDate & "','YYYY-MM-DD HH24:MI:SS')"

Anthony
0
 

Author Comment

by:joex
ID: 8225409
The first suggestion results in the following error:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01858: a non-numeric character was found where a numeric was expected"

The second suggestion results in the same error:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01858: a non-numeric character was found where a numeric was expected"

I had already tried both of the above, but I retried them just to be sure


The one SQL approach that might have promise is:

alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:mi:ss'

but I can't figure out how to issue it via ADO.
0
 

Author Comment

by:joex
ID: 8225736
The first suggestion results in the following error:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01858: a non-numeric character was found where a numeric was expected"

The second suggestion results in the same error:

"[Microsoft][ODBC driver for Oracle][Oracle]ORA-01858: a non-numeric character was found where a numeric was expected"

I had already tried both of the above, but I retried them just to be sure


The one SQL approach that might have promise is:

alter session set NLS_DATE_FORMAT = 'yyyy-mm-dd HH24:mi:ss'

but I can't figure out how to issue it via ADO.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 2000 total points
ID: 8226006
Use the following before the insert:
g_cn.Execute "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH:MI:SS' "

Anthony
0
 

Author Comment

by:joex
ID: 8226130
It seems to work fine.

Thanks.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month14 days, 13 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question