VB6 ADO command is inserting dates in American format instead of English

Question

I have a VB6 legacy program that has been quietly converting Now() Date to American format ie 8/4/2013 is inserted into an Access database as 4 August 2013 but 23/3/2013 is fine. This has been happening since 2002 but nobody noticed until now. Most updates were done at the end of the month.

If I extract the Procedure and run it in a dummy program it works fine. But somewhere in the application code something is persuading ADO to try and convert the date.

It is done in a ADO Command and the date is set up as a DateTimeStamp parameter

eg
strSQL = "Insert into TSCDATA " _
                & " ( FID, FNAME, FPID, FCREATEDATE, FLNKEDTDATE, FSIZE)" _
                & " values ( ? , ? , ? , ? , ? , ? ) "
with mADOCmdInsert
    .Name = CMD_INSERT
    .CommandText = strSQL
    .CommandType = adCmdText
    .ActiveConnection = m_conn
    .Prepared = False
    .Parameters.Append .CreateParameter(prID, adInteger, adParamInput)
    .Parameters.Append .CreateParameter(prNAME, adChar, adParamInput, 16)
    .Parameters.Append .CreateParameter(prFPID, adInteger, adParamInput)
    .Parameters.Append .CreateParameter(prFCREATEDATE, adDBTimeStamp, adParamInput)
    .Parameters.Append .CreateParameter(prFLNKEDTDATE, adChar, adParamInput, 8)
    .Parameters.Append .CreateParameter(prFSIZE, adChar, adParamInput, 8)    
End With

Elsewhere in the module:

dteNow = Now

With mADOCmdInsertScorpat
    .Parameters(prID).Value = lngId
    .Parameters(prNAME).Value = txtName
    .Parameters(prFPID).Value = VersionId
    .Parameters(prFCREATEDATE).Value = dteNow
    .Parameters(prFLNKEDTDATE).Value = ""
    .Parameters(prFSIZE).Value = txtSize

    .Execute lngRecsAffected
End With

Has anyone come across this before and now why this is happening?

Thanks

Gord Thompson · Answer 1 · 2013-04-16T12:12:01.193

I have been unsuccessful in recreating your issue using the code provided in the question. I tried

various combinations of Regional Settings in the Windows Control Panel: "English (Australia)", "English (Canada)", "English (United Kingdom)", "English (United States)", along with
both OLEDB and ODBC connections, and
I even back-dated the system clock in the virtual machine to April 2 to see if it was a Now() oddity.

In every case the ADO parameterized query inserted the correct date.

My suggestion would be to search the rest of the code for a rogue INSERT or UPDATE statement that passes a date literal directly to Jet. I'm quite certain that for any statement of the form...

INSERT INTO TSCDATA (FCCREATEDATE) VALUES (#2/04/2013 6:43:19 AM#)

...as produced by something like the following (in Australia)...

"INSERT INTO TSCDATA (FCCREATEDATE) VALUES (#" & CStr(Now) & "#)"

...Jet will always interpret the date as February 4 and not April 2, regardless of the Regional Settings for the date format.

I stepped through the code. Though other tables are updated as part of the transaction there are no obvious clues. Like you I have tried to recreate the error outside of the application without success. Next week I will try and use dynamic SQL instead of an ADO Command to see if that works as a workaround. In the meantime the user will only be able to use the that part of the application after the 12th of the month. :-) — Roy Bleasdale, Apr 17 '13 at 00:51

VB6 ADO command is inserting dates in American format instead of English

1 Answers1