CODEWITHSUNDEEP
sqlexceldatetimevb6
stack_pointer is EXTINCT
stack_pointer is EXTINCT

Reputation: 2393

Date format in VB6

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?

Upvotes: 0

Views: 11457

Answers (5)

a programmer
a programmer

Reputation: 959

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")

Upvotes: 2

Gabbarnet
Gabbarnet

Reputation:

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.

Upvotes: 1

onedaywhen
onedaywhen

Reputation: 57053

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.

Upvotes: 0

MicSim
MicSim

Reputation: 26806

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.

Upvotes: 1

divinci
divinci

Reputation: 23169

Dim oracleDate As String Dim excelDate As DateTime

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

Upvotes: 1

Related Questions