Postgres Dates

Databases
1

I was going through some code in Xojo (2013r3.3) to optimize it and I happened upon something that I didn’t expect.

I found some code that is posting to a Postgres 9.1 data file, similar to this:

//Listbox1.Cell(i,0) = "05/05/2017"
//startdate field in Postgres is a date field

rs.edit
rs.Field("startdate").StringValue = Listbox1.Cell(i,0)
rs.Update

I looked into the Postgres data and found ‘2017-05-05’. Somewhere the date is being converted from ‘05/05/2017’ to ‘2017-05-05’.

I did not even realize this was possible. I thought you had to enter the date in SQL format (‘YYYY-MM-DD’).

So where is this date conversion being handled - in Xojo, in Postgres?

Edit: Found the code where the data was being converted. It was all me.

2

This conversion is handled by PostgreSQL. Each datatype has a textual repesention for input / output. In your case see
here. For dates the interpretation depends on the DateStyle Parameter (GUC) which can be set on several levels, including per session The Xojo PostgreSQLDatabase plugin may set this implicitly upon .Connect but I cannot verify that in 2013r3.3. Please note also that PostgreSQL 9.1 is end-of-life and will not receive further updates. You should consider updating.

3

Rather than trying to feed a date string to PG, you should convert it to a date object in the client (via parsedate) and then say:

rs.Field(“startdate”).DateValue = myParsedDateObject