?
Solved

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

Posted on 2003-03-27
11
Medium Priority
?
2,508 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
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month8 days, 9 hours left to enroll

621 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