Re: VBA ADO Command Date Parameter Not Working

Richard Broersma wrote:
> on Mon, Nov 23, 2009 at 7:47 AM, Josh T <mortonjt(at)rochester(dot)rr(dot)com> wrote:
>> What is the correct way to pass a date into a parameter so it gets
>> into the database correctly?
>
> http://www.postgresql.org/docs/8.4/interactive/datetime-appendix.html
>
> This is how Postgresql accepts dates by default. There is a way to change this:
>
> http://www.postgresql.org/docs/8.4/interactive/datatype-datetime.html#DATATYPE-DATETIME-INPUT
> http://www.postgresql.org/docs/8.4/interactive/runtime-config-client.html#GUC-DATESTYLE
>
>> [5.750]conn=03044168, query='INSERT INTO test_table (test_date) VALUES
>> ('1900-05-07'::date);'
>
> It looks like the ODBC driver is make the conversion here.
> Rather than passing the date in with the #...#. Could you pass it in
> as a string?
>

Thanks, but trying as string gives me the same result

cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
adParamInput, 4, "2009-11-23")
-> results in 1900-05-07 in the DB

I also get the same result in I leave the value out entirely

cmd.Parameters.Append cmd.CreateParameter("@test_date", adDBDate,
adParamInput)
-> also results in 1900-05-07 in the DB

Trying a string this way doesn't work at all

cmd.Parameters.Append cmd.CreateParameter("@test_date", adVarChar,
adParamInput, Len("2009-11-23"), "2009-11-23")
-> throws an error on cmd.Execute -- ERROR: invalid input syntax for
type date: "128"; Error while executing the query

Thanks,
Josh