You are not logged in.

#1 2008-05-23 13:07:33

zenlord
Member
From: Belgium
Registered: 2006-05-24
Posts: 1,221
Website

PostgreSQL date: invalid input syntax

I recently started a file-program to use in my office. It is being built with PHP, PostgreSQL, TBS templates, LDAP etc. I know the basics of PHP, but this is my first project using pgSQL and this templating system, which looked good and easy during the first days of development.

I now get a 'date: invalid input syntax'-error and I cannot seem to get it ironed out:
In my table I have a column with dates (formatted 'yyyy-mm-dd' as is recommended in pgSQL). These dates can be listed perfectly with PHP. The properties of the column look fine.

In my template I need to use the MergeBlock-function and I would like to only list the files opened in 2008 f.e., which results in this SELECT:
'SELECT * FROM dossiers WHERE EXTRACT FROM DATE(ISOYEAR, \'dossiers_open\')=2008'

If I remove the quotes, then I get a whole list of errors of non-existing stuff, and leaving in the quotes implies that the column with the dates isn't formatted correctly. Did I miss something or is this a known problem with a known solution?

Maybe it is relevant that I dumped the dbase in Debian with pgSQL 7 and imported it under Archlinux with pgSQL 8.3. Also, in Archlinux I specified UTF-8 as the encoding, where I don't know what the encoding was in Debian - I have seen some odd characters in another column, so that also could be a problem?

THX for reading!

Offline

#2 2008-05-23 20:26:43

zenlord
Member
From: Belgium
Registered: 2006-05-24
Posts: 1,221
Website

Re: PostgreSQL date: invalid input syntax

OK, made some progress. I am now convinced that it is not the date formatting that is causing this error, but the fact that my function takes the column name and wants to treat is as a date instead of pulling the value of that column. It's been a long time since my previous project and I really don't seem to get closer to a solution. Any advice please?

Function:

$TBS->MergeBlock('overzicht_dossiers',$cnx_id,'SELECT * FROM dossiers WHERE date_part(\'year\', DATE \'dossiers_open\')=2005 ORDER BY dossiers_id DESC');

The problem is that "date_part(\'year\', DATE \'dossiers_open\')=2005" should be true for dossiers_open-values of 2005, but the function won't fetch me the value of 'dossiers_open': it returns the error:

ERROR: invalid input syntax for type date: "dossiers_open"

...

Zl.

Last edited by zenlord (2008-05-23 20:27:21)

Offline

Board footer

Powered by FluxBB