Have Geek Brain

Date format in VB6

I am using VB6 for my application. I have populated Excel using a RecordSet obtained from a SQL query.

One column named Time_period

has values ​​like

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

I need to pass this data to another SQL query for processing. I'm a little confused about the formats as Oracle accepts inputs like "23-Jul-2009", "02-Jan-1998", etc.

Can you help convert from one format to another in VB6?

0


a source to share


5 answers


Use format.

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

This will work in your case.



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

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


a source


Dim oracleDate As String Dim excelDate As DateTime



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


a source


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

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

You can then pass that value into your SQL query along with the appropriate date separators. (if no parameters are used)

You can also check for null values ​​as they won't work with the above function.

+1


a source


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

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 it helps.

+1


a source


You can usually "pipe" the result of one query to the other by creating a JOIN between the two in SQL. Hopefully the date values ​​in the column are Time_period

already of temporary type; if they cannot be applied to one using SQL. Even if the data is in a different database (like Oracle and Excel), you could use ACE / Jet (MS Access) to create a join query for the two, either directly or via a signed table. More details on what you are trying to achieve please.

0


a source






More articles:


All Articles