You are not logged in.
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
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