Обсуждение: 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