Date format in VB6

Question

I am using VB6 for my application. I've populated Excel with the RecordSet obtained from a SQL query.

One column called Time_period has values like

"2/31/2006"
"12/29/2000"
etc.

I need to pass these inputs to another SQL query for processing. I am little confused with the formats, as Oracle accepts inputs of type "23-Jul-2009", "02-Jan-1998" and so on.

Can you help converting from the one format to the other in VB6?

Answer 1

Hi there,

Dim oracleDate As String
Dim excelDate As DateTime

oracleDate = Format$(excelDate , "dd-mmm-yyyy")

Answer 2

Use Format.

Debug.Print Format$(Now, "dd-mmm-yyyy")

This will work in your case.

You could try using the following format (ISO standard):

Debug.Pring Format$(Now, "yyyy-MM-dd")

Answer 3

If you get your date from a recordset, you can store it in a date variable and just call the following formatting function to get the string representation you like:

Format(myDateVar, "dd-mmm-yyyy")

Then you can pass this value to your SQL query along with the proper date delimiters. (if not using parameters)

You might also want to check for null values, as they will not work with above function.

Answer 4

One would normally 'pass' the resultset of one query to another by creating a JOIN between the two in SQL code. Hopefully, the date values in column Time_period are already of a temporal type; if not they can be cast to one using SQL. Even if the data is in different database (e.g. Oracle and Excel) you could be able to use ACE/Jet (a.k.a. MS Access) to create a query to join the two, either directly or via a inked table. More detail of what you are trying to achieve, please.

Answer 5

Here is a code snippet from Excel VBA, should work in VB6 with some tweaks.

Sub temp()
    Dim lConn, lRs, sSQL As String
    Set lConn = CreateObject("ADODB.Connection")
    Set lRs = CreateObject("ADODB.Recordset")

    lConn.Open "DSN=yourdns; UID=youruid; PWD=yourpwd;"

    Dim oWS As Worksheet
    Set oWS = Worksheets(1)

    sSQL = " SELECT * FROM Product WHERE Last_Upd_Date > to_date('" & oWS.Range("A1").Value & "', 'MM-DDD-YYYY') "
    lRs.Open sSQL, lConn

    Debug.Print lRs.EOF
    lRs.Close
    lConn.Close
End Sub

Hope this helps.