Re: Syntax/Format of Date Literals

From: Paul Walker <walkerp1_at_yahoo.com>
Date: 17 May 2002 19:50:28 -0700
Message-ID: <9cef9a4a.0205171850.552cb1d4_at_posting.google.com>

scotterickson_at_mail.com (Scott) wrote in message news:<3628077d.0205170458.2b26c9fd_at_posting.google.com>...
> I'm using ADO to read data from an Oracle table. The Oracle table has
> a CREATED_ON field that is a date/time format. I need to add
> selection criteria to my WHERE clause that selects records created
> within a specific date/time range. I tried using the TO_DATE PLSQL
> function, but that chops the time portion off my VB date/time
> variable. My ADO looks something like this:
>
> SELECT * FROM tbl
> WHERE
> tbl.CREATED_ON BETWEEN TO_DATE('" & format(dte1, "dd-mmm-yyyy") &
> "') " & _
> "AND TO_DATE('" & format(dte2, "dd-mmm-yyyy") & "')"
>
> my problem is that by formating my visual basic date time variable to
> a format that oracle will accept as input to the TO_DATE function,
> drops the time portion of my variable.

Scott,

You'll want to extend those format statements to include the time strings and use bind variables for Oracle server efficiency (as mentioned in Jim's post). Here's a short example that worked well for me (i386 VB6 SP5).

Regards,
Paul KE Walker

Dim adoCon As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim adoRS As New ADODB.Recordset
Dim dte1, dte2 As Date

dte1 = #1/1/2000#
dte2 = #12/31/2005 11:59:59 PM#

' Connect via ODBC...A DSN could work here as well adoCon.ConnectionString = "UID=notmyuser;PWD=notmypass;" & _   "DRIVER={Oracle ODBC Driver};DATABASE=TSTDB1" adoCon.Open

' Basic command setup. Note that the ?s let us use bind variables for ' Oracle
With adoCmd

  .ActiveConnection = adoCon
  .CommandType = adCmdText
  .CommandText = "SELECT * FROM tbl Where tbl.CREATED_ON BETWEEN " & _

    "TO_DATE(?,'dd-mon-yyyy hh24:mi:ss') AND " & _     "TO_DATE(?,'dd-mon-yyyy hh24:mi:ss')"   .Parameters.Append .CreateParameter(, adVarChar, , 20, _     Format(dte1, "dd-mmm-yyyy Hh:Nn:Ss"))   .Parameters.Append .CreateParameter(, adVarChar, , 20, _     Format(dte2, "dd-mmm-yyyy Hh:Nn:Ss")) End With

adoRS.Open adoCmd

' Your record processing goes here.
While Not adoRS.EOF
  Debug.Print adoRS.Fields(0)
  adoRS.MoveNext
Wend

adoRS.Close
adoCon.Close Received on Sat May 18 2002 - 04:50:28 CEST