pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedstring to date conversion
6+ messages / 4 participants
[nested] [flat]
* string to date conversion
@ 2005-04-22 08:43 Jerome Alet <alet@librelogiciel.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jerome Alet @ 2005-04-22 08:43 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: string to date conversion
@ 2005-04-22 09:08 Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 1 reply; 6+ messages in thread
From: Ramakrishnan Muralidharan @ 2005-04-22 09:08 UTC (permalink / raw)
To: Jerome Alet <alet@librelogiciel.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: string to date conversion
@ 2005-04-22 09:13 Jerome Alet <alet@librelogiciel.com>
parent: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jerome Alet @ 2005-04-22 09:13 UTC (permalink / raw)
To: Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: string to date conversion
@ 2005-04-22 09:21 Christoph Haller <ch@rodos.fzk.de>
parent: Jerome Alet <alet@librelogiciel.com>
0 siblings, 1 reply; 6+ messages in thread
From: Christoph Haller @ 2005-04-22 09:21 UTC (permalink / raw)
To: Jerome Alet <alet@librelogiciel.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: string to date conversion
@ 2005-04-22 09:30 Jerome Alet <alet@librelogiciel.com>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 6+ messages in thread
From: Jerome Alet @ 2005-04-22 09:30 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: string to date conversion
@ 2005-04-22 10:22 Michael Fuhr <mike@fuhr.org>
parent: Jerome Alet <alet@librelogiciel.com>
0 siblings, 0 replies; 6+ messages in thread
From: Michael Fuhr @ 2005-04-22 10:22 UTC (permalink / raw)
To: Jerome Alet <alet@librelogiciel.com>; +Cc: Christoph Haller <ch@rodos.fzk.de>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-04-22 10:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-04-22 08:43 string to date conversion Jerome Alet <alet@librelogiciel.com>
2005-04-22 09:21 ` Christoph Haller <ch@rodos.fzk.de>
2005-04-22 09:30 ` Jerome Alet <alet@librelogiciel.com>
2005-04-22 10:22 ` Michael Fuhr <mike@fuhr.org>
2005-04-22 09:08 Re: string to date conversion Ramakrishnan Muralidharan <ramakrishnanm@pervasive-postgres.com>
2005-04-22 09:13 ` Jerome Alet <alet@librelogiciel.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox