Trying to figure out how to set this insert.....

I have one column that is set to date, NOT NULL. When 09/20/02(for September 02, 2002)is input on the websiete, Postgres reads it as 2020-02-09. Does anyone know how I can either change the database to read it right or change the insert statement to make it go in right?

    5 days later

    Well, the insert works now, but the database keeps reverting to an old datestyle. Is there somewhere I can permanently change this?

    warehouse=# show datestyle;
    NOTICE: DateStyle is ISO with European conventions
    SHOW VARIABLE
    warehouse=# set datestyle to 'SQL, US';
    SET VARIABLE
    warehouse=# show datestyle;
    NOTICE: DateStyle is SQL with US (NonEuropean) conventions
    SHOW VARIABLE
    warehouse=# \q
    aaron@wmscentral:/var/www/frame$ psql warehouse
    Welcome to psql, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    warehouse=# show datestyle;
    NOTICE: DateStyle is ISO with European conventions
    SHOW VARIABLE

      OK, as the postgres superuser, shut down your database, edit postgresql.conf, and restart the database

      pg_ctl stop
      vi $PGDATA/postgresql.conf

      then change the line:

      #datestyle = 'iso, us'

      to

      datestyle = 'sql, us'

      and restart your database:

      pg_ctl start

      That should do it.

        • [deleted]

        • Post #5

        Changing the database's datestyle is one option, but it requires a restart and will have to be done on every server that runs this script.

        Another option is to use to_char() in the SELECT statement to redefine the format of the date as it is put into the resultset. That will work no matter how the database is configured, and (obviously) does not require a restart.

        Read the postgresql documentation about date formats for more info.

          I tried the to_char method, but since this is for many php pages over only a few databases, I think editing the conf file better for the long term. Thanks for your help guys 🙂

            Write a Reply...