VB & Database, US/UK Date corruption: Jet 3.5 stored procs via Jet 4

US/UK Date corruption: Jet 3.5 stored procs via Jet 4

Author	Message
Douglas J. Steel #1 / 4	US/UK Date corruption: Jet 3.5 stored procs via Jet 4 My understanding is that, in Access SQL, dates are expected to be in mm/dd/yyyy format, regardless of what the Regional Settings are. I suspect that the transformation is actually happening when you're writing to the database, not when you're getting the value back. While I can't be sure (since I've so far been successful in avoiding Access 2000), I suspect it will be exactly the same there, as this is considered to be "as designed". -- Doug Steele, Microsoft Access MVP Beer, Wine and Database Programming. What could be better? Visit "Doug Steele's Beer and Programming Emporium" http://www.--*.com/ Quote: > When VB6 sends a Date value to a parameterised query in an Access 97 > database via the "Microsoft.Jet.OLEDB.4.0" provider for ADO, it gets > corrupted, as if the day and month values were interchanged. > I set up this query in DateBug.mdb: > PARAMETERS DateOut DateTime; > SELECT [DateOut] AS DateBack, Now AS NowBack > FROM OneRecord; > where OneRecord is a table with just one record (like DUAL in Oracle?). > It "bounces" the parameter value back as a literal attribute, and sends > Now() just so we can see that the corruption happens from VB to Jet, > not on the way back. > Here's the VB code: > Dim cnn As New ADODB.Connection > Dim cmd As New ADODB.Command > Dim DateOut As Date > Dim rst As ADODB.Recordset > cnn.Provider = "Microsoft.Jet.OLEDB.4.0" > cnn.Open "DateBug.mdb" > Set cmd.ActiveConnection = cnn > cmd.CommandText = "EchoDate" > DateOut = Now > Set rst = cmd.Execute(, Array(DateOut), adCmdStoredProc) > Debug.Print "DateOut=" & Format$(DateOut, "Long Date") > Debug.Print "DateBack=" & Format$(rst!DateBack, "Long Date") > Debug.Print "NowBack=" & Format$(rst!NowBack, "Long Date") > and here's what MsgBox reports: > DateOut=2 August 2000 (i.e. 2/8/2000 UK=style, 8/2/2000 US-style) > DateBack=8 February 2000 (i.e. 8/2/2000 UK-style; 2/8/2000 US-style) > NowBack=2 August 2000 > Since it sends Now back OK, this suggests that DateOut gets garbled on the way > out. > I'm using: > NT4 SP6a > VB6 SP4 > MDAC 2.50.4403.12 (according to Component Checker) > Access 97 SR-2a > and I really want to use parameterised queries... > Can anyone suggest a workaround, or confirm that Access 2000 databases don't > have this problem. Or tell me how to report this to Microsoft. > (I checked the KB online as best I could) > Paul Singleton (new to this group)
Sat, 18 Jan 2003 03:00:00 GMT

Paul Singleto #2 / 4	US/UK Date corruption: Jet 3.5 stored procs via Jet 4 When VB6 sends a Date value to a parameterised query in an Access 97 database via the "Microsoft.Jet.OLEDB.4.0" provider for ADO, it gets corrupted, as if the day and month values were interchanged. I set up this query in DateBug.mdb: PARAMETERS DateOut DateTime; SELECT [DateOut] AS DateBack, Now AS NowBack FROM OneRecord; where OneRecord is a table with just one record (like DUAL in Oracle?). It "bounces" the parameter value back as a literal attribute, and sends Now() just so we can see that the corruption happens from VB to Jet, not on the way back. Here's the VB code: Dim cnn As New ADODB.Connection Dim cmd As New ADODB.Command Dim DateOut As Date Dim rst As ADODB.Recordset cnn.Provider = "Microsoft.Jet.OLEDB.4.0" cnn.Open "DateBug.mdb" Set cmd.ActiveConnection = cnn cmd.CommandText = "EchoDate" DateOut = Now Set rst = cmd.Execute(, Array(DateOut), adCmdStoredProc) Debug.Print "DateOut=" & Format$(DateOut, "Long Date") Debug.Print "DateBack=" & Format$(rst!DateBack, "Long Date") Debug.Print "NowBack=" & Format$(rst!NowBack, "Long Date") and here's what MsgBox reports: DateOut=2 August 2000 (i.e. 2/8/2000 UK=style, 8/2/2000 US-style) DateBack=8 February 2000 (i.e. 8/2/2000 UK-style; 2/8/2000 US-style) NowBack=2 August 2000 Since it sends Now back OK, this suggests that DateOut gets garbled on the way out. I'm using: NT4 SP6a VB6 SP4 MDAC 2.50.4403.12 (according to Component Checker) Access 97 SR-2a and I really want to use parameterised queries... Can anyone suggest a workaround, or confirm that Access 2000 databases don't have this problem. Or tell me how to report this to Microsoft. (I checked the KB online as best I could) Paul Singleton (new to this group)
Sun, 19 Jan 2003 03:00:00 GMT

Peter Ro #3 / 4	US/UK Date corruption: Jet 3.5 stored procs via Jet 4 Hmmm.... Have you tried using a different field name instead of Now ?? Don't forget that in Access you can actually have VBA code functions in your SQL. Since "Now" is a VBA function this is what might be giving you the weird results, because it is actually running the Now function and confusing that with your field. Bye Peter Quote: > When VB6 sends a Date value to a parameterised query in an Access 97 > database via the "Microsoft.Jet.OLEDB.4.0" provider for ADO, it gets > corrupted, as if the day and month values were interchanged. > I set up this query in DateBug.mdb: > PARAMETERS DateOut DateTime; > SELECT [DateOut] AS DateBack, Now AS NowBack > FROM OneRecord; > where OneRecord is a table with just one record (like DUAL in Oracle?). > It "bounces" the parameter value back as a literal attribute, and sends > Now() just so we can see that the corruption happens from VB to Jet, > not on the way back. > Here's the VB code: > Dim cnn As New ADODB.Connection > Dim cmd As New ADODB.Command > Dim DateOut As Date > Dim rst As ADODB.Recordset > cnn.Provider = "Microsoft.Jet.OLEDB.4.0" > cnn.Open "DateBug.mdb" > Set cmd.ActiveConnection = cnn > cmd.CommandText = "EchoDate" > DateOut = Now > Set rst = cmd.Execute(, Array(DateOut), adCmdStoredProc) > Debug.Print "DateOut=" & Format$(DateOut, "Long Date") > Debug.Print "DateBack=" & Format$(rst!DateBack, "Long Date") > Debug.Print "NowBack=" & Format$(rst!NowBack, "Long Date") > and here's what MsgBox reports: > DateOut=2 August 2000 (i.e. 2/8/2000 UK=style, 8/2/2000 US-style) > DateBack=8 February 2000 (i.e. 8/2/2000 UK-style; 2/8/2000 US-style) > NowBack=2 August 2000 > Since it sends Now back OK, this suggests that DateOut gets garbled on the way > out. > I'm using: > NT4 SP6a > VB6 SP4 > MDAC 2.50.4403.12 (according to Component Checker) > Access 97 SR-2a > and I really want to use parameterised queries... > Can anyone suggest a workaround, or confirm that Access 2000 databases don't > have this problem. Or tell me how to report this to Microsoft. > (I checked the KB online as best I could) > Paul Singleton (new to this group)
Sun, 19 Jan 2003 03:00:00 GMT

Joe Mello #4 / 4	US/UK Date corruption: Jet 3.5 stored procs via Jet 4 Hello Paul, whenever you send a query to SQl with a date in it format the date 1st ie. -This Was From Your Code Below DateOut = Now -It Should be DateOut = Format(Now,"mm/dd/yyyy") Basically convert the date to American format 1st as much as it hurts :) Hope This Helps Regards Joe Quote: > When VB6 sends a Date value to a parameterised query in an Access 97 > database via the "Microsoft.Jet.OLEDB.4.0" provider for ADO, it gets > corrupted, as if the day and month values were interchanged. > I set up this query in DateBug.mdb: > PARAMETERS DateOut DateTime; > SELECT [DateOut] AS DateBack, Now AS NowBack > FROM OneRecord; > where OneRecord is a table with just one record (like DUAL in Oracle?). > It "bounces" the parameter value back as a literal attribute, and sends > Now() just so we can see that the corruption happens from VB to Jet, > not on the way back. > Here's the VB code: > Dim cnn As New ADODB.Connection > Dim cmd As New ADODB.Command > Dim DateOut As Date > Dim rst As ADODB.Recordset > cnn.Provider = "Microsoft.Jet.OLEDB.4.0" > cnn.Open "DateBug.mdb" > Set cmd.ActiveConnection = cnn > cmd.CommandText = "EchoDate" > DateOut = Now > Set rst = cmd.Execute(, Array(DateOut), adCmdStoredProc) > Debug.Print "DateOut=" & Format$(DateOut, "Long Date") > Debug.Print "DateBack=" & Format$(rst!DateBack, "Long Date") > Debug.Print "NowBack=" & Format$(rst!NowBack, "Long Date") > and here's what MsgBox reports: > DateOut=2 August 2000 (i.e. 2/8/2000 UK=style, 8/2/2000 US-style) > DateBack=8 February 2000 (i.e. 8/2/2000 UK-style; 2/8/2000 US-style) > NowBack=2 August 2000 > Since it sends Now back OK, this suggests that DateOut gets garbled on the way > out. > I'm using: > NT4 SP6a > VB6 SP4 > MDAC 2.50.4403.12 (according to Component Checker) > Access 97 SR-2a > and I really want to use parameterised queries... > Can anyone suggest a workaround, or confirm that Access 2000 databases don't > have this problem. Or tell me how to report this to Microsoft. > (I checked the KB online as best I could) > Paul Singleton (new to this group)
Sun, 19 Jan 2003 03:00:00 GMT

Page 1 of 1

[ 4 post ]