Thread: string to date conversion
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
On Fri, Apr 22, 2005 at 02:38:43PM +0530, Ramakrishnan Muralidharan wrote: > Hi, > > I have tried the same in PostgreSQL 8.0.1 and it is working fine. I have used following example for testing > > create table test1 > ( > date varchar(10) > ) > > insert into test1 values('2005/04/22') > > select date::timestamp from test1 Sorry, I should have said that I use 7.4.7... I know it's old but as already explained I can't do much about it for now... Thanks for your help bye Jerome Alet
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
On Fri, Apr 22, 2005 at 11:21:26AM +0200, Christoph Haller wrote: > > select '2005/04/22'::text::timestamp ; > timestamp > --------------------- > 2005-04-22 00:00:00 > (1 row) > > works for me. It works fine !!! Thanks so much for your help. I missed the '::text' intermediate conversion it seems bye Jerome Alet
On Fri, Apr 22, 2005 at 11:30:02AM +0200, Jerome Alet wrote: > On Fri, Apr 22, 2005 at 11:21:26AM +0200, Christoph Haller wrote: > > > > select '2005/04/22'::text::timestamp ; > > timestamp > > --------------------- > > 2005-04-22 00:00:00 > > (1 row) > > > > works for me. > > It works fine !!! > > Thanks so much for your help. > > I missed the '::text' intermediate conversion it seems This is fixed in 8.0 (in 8.0.2, anyway). Casts from varchar no longer need an intermediate cast to text. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hi, I have tried the same in PostgreSQL 8.0.1 and it is working fine. I have used following example for testing create table test1 ( date varchar(10) ) insert into test1 values('2005/04/22') select date::timestamp from test1 Regards, R.Muralidharan -----Original Message----- From: Jerome Alet [mailto:alet@librelogiciel.com] Sent: Friday, April 22, 2005 2:13 PM To: pgsql-sql@postgresql.org Subject: [SQL] string to date conversion 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 ---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org