VBScript unaltered Date and Time for database migration

Question

I'm doing a MSSQL to MySQL data migration. My date/time value is exactly how it needs to be in both databases. However, VBScript converts this (by default):

2016-01-06 10:26:30.363

To this (which errors on INSERT):

1/6/2016 10:26:30 AM

I'm aware I can construct the value again with Year(), Month(), etc. Anyone know how to get this date/time value unaltered from the database using VBScript?

UPDATE: Per the "show my code" comment, here's a section:

strSQL = "SELECT * FROM Users"
Set objRS = CreateObject("ADODB.RecordSet") 
objRS.Open strSQL, objConn,3,3

Do While Not objRS.EOF

    sUserId = objRS("UserId")
    sCreatedDate = objRS("CreatedDate") '<-- At this point, it's "converted" already
    wscript.echo sCreatedDate      '<-- This displays 1/6/2016 10:26:30 AM format

    insertSQL = "INSERT INTO northwind.usersetting (UserId,CreatedDate) "
    insertSQL = insertSQL & "VALUES ('"&sUserId&"','"&sCreatedDate &"');"

    objConn2.Execute = (insertSQL)

objRS.MoveNext
Loop

Just your standard vbscript. I think I can get away with this in MSSQL, but MySQL doesn't like it. In the meantime, I have done this to work around the issue:

Function FormatDate4Insert(date)
    FormatDate4Insert = Year(date) & "-" & Month(date) & "-" & Day(date) & " " & Hour(date) & ":" & Minute(date) & ":" & Second(date)
End Function

I would love to do a straight non-conversion, but vbs seems to convert no matter what I do. I tried converting to string and a few other things with no joy...

P.S. I'm not sure what you mean by database import export mechanisms. However, I tried the MySQL migration tool as well as exporting and import mechanisms (to csv...with different delimiters and such... and even a json export, massaging the data with Notepad++ and Excel, etc) and can't get the data to jive with my selective import. I can migrate an entire database without issue for the most part, but simply want to do the data from an individual table. When I kill too much time, I usually just fall back to vbscript or whatever scripting makes sense.

I find both databases more forgiving and finicky in some areas. However, with MSSQL to MySQL, I have to convert empty values to "NULL" and True/False to their bit values (e.g. b'0') and other little tweaks that scripting makes easier (at least for me).

UPDATE 2: The error as requested:

Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 8.0(w) Driver][mysqld-8.0.16]Incorrect datetime value: '1/6/2016 10:26:30 AM' for column 'CreatedDate' at row 1.

I can't reproduce the other error, but I was also getting an error that was something similar to this:

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1/6/2016 10:26:30 AM'

That was in reference to the Insert statement.

Answer 1

Try a parameterized query:

    ' *** ADO ***
    '---- CommandTypeEnum Values ----
    Const adCmdText = &H0001

    '---- DataTypeEnum Values ----
    Const adInteger = 3
    Const adDate = 7

    '---- ParameterDirectionEnum Values ----
    Const adParamInput = &H0001

    Dim cmd
    Dim sSQL

    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = cn   ' Assumes cn is an actice ADOB.Connection object
    cmd.CommandType = adCmdText

    sSQL = "INSERT INTO northwind.usersetting (UserId, CreatedDate) VALUES (?, ?);"

    cmd.CommandText = sSQL

    Set prm = cmd.CreateParameter("UserId", adInteger, adParamInput, , UserId)  ' Assuming UserId is a integer, adjust as needed
    cmd.Parameters.Append prm

    Set prm = cmd.CreateParameter("CreatedDate", adDate, adParamInput, , CreatedDate)
    cmd.Parameters.Append prm

    cmd.Execute , , adExecuteNoRecords

[Added]

Try the following snippet just to make sure that MySQL accepts a date value:

Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cn   ' Assumes cn is an actice ADOB.Connection object
cmd.CommandType = adCmdText

sSQL = "INSERT INTO northwind.usersetting (CreatedDate) VALUES (?);"

cmd.CommandText = sSQL

Set prm = cmd.CreateParameter("CreatedDate", adDate, adParamInput, , Now())
cmd.Parameters.Append prm

cmd.Execute , , adExecuteNoRecords

If this works, than you might want to try the following line in the first sample I posted:

Set prm = cmd.CreateParameter("CreatedDate", adDate, adParamInput, , CDate(CreatedDate))

Note the explicit date conversion with CDate().

The ADO constants at the top of the first code snippet are taken from a file called adovbs.inc, distributed by Microsoft. Unfortunately a quick search didn't bring up a download from MS. But here's a gist with its contents. Scroll down to '---- DataTypeEnum Values ----. There are a few other date related constants, e.g. Const adDBDate = 133. You might try out those and see if it yields the expected result.

[Added 2]

' *** ADO ***
'---- CommandTypeEnum Values ----
Const adCmdText = &H0001

'---- DataTypeEnum Values ----
Const adInteger = 3
Const adDate = 7

'---- ParameterDirectionEnum Values ----
Const adParamInput = &H0001


Dim strSQL
Dim cmd, rs


Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = cnMSSQL   ' Assumes cnMSSQL is an actice ADOB.Connection object connected to the MS SQL server
cmd.CommandType = adCmdText

' Fill the recordset from a command object connecting to the source MS SQL
strSQL = "SELECT * FROM Users"
cmd.CommandText = strSQL
Set rs = cmd.Execute()

' Now use the Command object to fill the MySQL DB
Set cmd.ActiveConnection = cnMySQL  ' ' Assumes cnMySQL is an actice ADOB.Connection object connected to the MySQL server
strSQL = "INSERT INTO northwind.usersetting (UserId, CreatedDate) VALUES (?, ?);"
cmd.CommandText = strSQL

Do While Not rs.EOF

    ' This assumes that the columns in source MS SQL also are named 'UserId' and 'CreateDate'.
    ' Adjust the rs.Fields("UserId").Value and rs.Fields("CreateDate").Value as needed

    Set prm = cmd.CreateParameter("UserId", adInteger, adParamInput, , rs.Fields("UserId").Value)  ' Assuming UserId is a integer, adjust as needed
    cmd.Parameters.Append prm

    Set prm = cmd.CreateParameter("CreatedDate", adDate, adParamInput, , rs.Fields("CreateDate").Value)
    cmd.Parameters.Append prm

    cmd.Execute , , adExecuteNoRecords  

    ' Clear/reset the parameter collection

    rs.MoveNext

Loop