Skip site navigation (1) Skip section navigation (2)

VBA ADO Command Date Parameter Not Working

From: Josh T <mortonjt(at)rochester(dot)rr(dot)com>
To: pgsql-odbc(at)postgresql(dot)org
Subject: VBA ADO Command Date Parameter Not Working
Date: 2009-11-23 15:47:41
Message-ID: (view raw or whole thread)
Lists: pgsql-odbc

I'm using the PSQL-ODBC driver from Excel 2002 VBA with a ADO Command 
object.  What is the correct way to pass a date into a parameter so it 
gets into the database correctly? - everything I've tried results in 
1900-05-07 added to the DB instead of the correct date. I also get 
1900-05-07 in the DB if I leave the date empty.  I've simplified my code 
into the following test case - I've had no problems with other data 
types.  I've already tried changing adDBDate, passing Date objects or 


Sub TestDateInsert()
     Dim conn As ADODB.Connection
     Dim cmd As ADODB.Command

     Set conn = New ADODB.Connection
     conn.Open "DSN=PRHTest"

     Set cmd = New ADODB.Command
     cmd.ActiveConnection = conn
     cmd.CommandType = adCmdText
     cmd.CommandText = "INSERT INTO test_table (test_date) VALUES (?);"
     cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
adParamInput, , #11/23/2009#)
     cmd.Execute , adExecuteNoRecords

     Set cmd = Nothing
     Set conn = Nothing

End Sub

Database Table definition:

CREATE TABLE test_table (
	test_date date NOT NULL

 From psqlodbc.log

[0.000]Driver Version='08.04.0100,200907060001' linking 1400 static
Multithread library
[0.000]Global Options: fetch=100, socket=4096, unknown_sizes=0,
max_varchar_size=255, max_longvarchar_size=8190
[0.016]                disable_optimizer=0, ksqo=1, unique_index=1,
[0.016]                text_as_longvarchar=1, unknowns_as_longvarchar=0,
bools_as_char=1 NAMEDATALEN=64
[0.016]                extra_systable_prefixes='dd_;', conn_settings=''
[0.578]    [ PostgreSQL version string = '8.3.8' ]
[0.578]    [ PostgreSQL version number = '8.3' ]
[0.578]conn=03044168, query='select oid, typbasetype from pg_type where
typname = 'lo''
[0.594]    [ fetched 0 rows ]
[0.594]    [ Large Object oid = -999 ]
[0.594]    [ Client encoding = 'UTF8' (code = 6) ]
[5.750]conn=03044168, query='INSERT INTO test_table (test_date) VALUES
[7.203]conn=03044168, PGAPI_Disconnect


pgsql-odbc by date

Next:From: Jan-Peter SeifertDate: 2009-11-23 16:08:05
Subject: SSL timeout?
Previous:From: Hiroshi InoueDate: 2009-11-22 00:21:16
Subject: Re: declare/fetch problem with pgpool 2.2.3; Postgres 8.4; ODBC

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group