X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D456153B0C for ; Fri, 22 Apr 2005 06:22:02 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 64503-01 for ; Fri, 22 Apr 2005 09:22:01 +0000 (GMT) Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by svr1.postgresql.org (Postfix) with SMTP id D92B353B07 for ; Fri, 22 Apr 2005 06:21:58 -0300 (ADT) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Fri Apr 22 11:21:28 2005 +0200 Received: from rodos.fzk.de (resy6.fzk.de [141.52.68.10]) by resy5.fzk.de (8.12.7/8.12.7/SuSE Linux 0.6) with ESMTP id j3M9LRsG003455; Fri, 22 Apr 2005 11:21:27 +0200 Message-ID: <4268C216.8BC93C10@rodos.fzk.de> Date: Fri, 22 Apr 2005 11:21:26 +0200 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: Jerome Alet Cc: pgsql-sql@postgresql.org Subject: Re: string to date conversion References: <20050422084301.GB12176@mail.librelogiciel.com> Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS - amavis-milter (http://www.amavis.org/) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.027 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200504/323 X-Sequence-Number: 21216 Jerome Alet wrote: > > Hi, > > I'm working on an existing Pg database which, please don't ask why, > stores dates as varchar attributes in the form 'YYYY/MM/DD' > > I'm not allowed to modify the tables to use 'timestamp' instead, > so I'd like to convert on the fly when retrieving datas with > select. > > When I do : > > select mydatefield::timestamp from mytable; > > There's an error saying that converting from varchar to date is > not possible > > How can I do this ? > > Thanks in advance > > Jerome Alet > select version(); version ------------------------------------------------------------------------------------- PostgreSQL 7.4.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.1 (SuSE Linux) (1 row) select '2005/04/22'::text::timestamp ; timestamp --------------------- 2005-04-22 00:00:00 (1 row) works for me. Regards, Christoph