Thread: Need some help with dates
Hi.
First, let me apologize for my poor level in english, I hope you'll
understand me.
I'm new in PL/PgSQL, and I have a little problem.
I have a temporary table (temp) with only varchar. I must do a request that
fill another table (mclis) with temp's data. But on the new tables, I have a
field in date format. When I make a
update mclis
set cli_date = temp.temp1
he doesn't want because of the different formats ('column "cli_date" is of
type 'date' but expression is of type 'character varying').
In temp, my data are like 01/01/2001
I tried also
set cli_date = select case(temp.temp1 as date) but he says:
Cannot cast type 'character' to 'date' or Cannot cast type 'character
varying' to 'date'.
Does anyone has an idea ?
Hi Stéphane,
Use,
update mclis
set cli_date = to_date(temp.temp1,'dd/mm/yyyy')
Regards,
Gautham.
----- Original Message -----
From: "Stéphane DEWITTE" <stephane@smeso.fr>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 17, 2002 6:03 PM
Subject: [SQL] Need some help with dates
> Hi.
>
> First, let me apologize for my poor level in english, I hope you'll
> understand me.
>
> I'm new in PL/PgSQL, and I have a little problem.
>
> I have a temporary table (temp) with only varchar. I must do a request
that
> fill another table (mclis) with temp's data. But on the new tables, I have
a
> field in date format. When I make a
>
> update mclis
> set cli_date = temp.temp1
>
> he doesn't want because of the different formats ('column "cli_date" is of
> type 'date' but expression is of type 'character varying').
>
> In temp, my data are like 01/01/2001
>
> I tried also
> set cli_date = select case(temp.temp1 as date) but he says:
> Cannot cast type 'character' to 'date' or Cannot cast type 'character
> varying' to 'date'.
>
> Does anyone has an idea ?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
> -----Message d'origine----- > De : Gautham S. Rao [mailto:gautam.rao@tatainfotech.com] > Envoyé : mercredi 17 avril 2002 15:00 > À : Stéphane DEWITTE; pgsql-sql@postgresql.org > Objet : Re: [SQL] Need some help with dates > > > Hi Stéphane, > > Use, > update mclis > set cli_date = to_date(temp.temp1,'dd/mm/yyyy') > > Regards, > Gautham. > > ----- Original Message ----- > From: "Stéphane DEWITTE" <stephane@smeso.fr> > To: <pgsql-sql@postgresql.org> > Sent: Wednesday, April 17, 2002 6:03 PM > Subject: [SQL] Need some help with dates Thanks a lot. I knew it was existing in Orcale, but I haven't find any documentation on this function. I have another problem of that style, I must convert a varchar to a numeric. It seems that to_num or to_number doesn't exists, and cast tells me "Cannot cast type 'character varying' to 'numeric'". Does another function for that exists ? And where could I find a list of existing functions in Postgres 7.2 ? Regards, Stéphane.
Hi Stéphane,
to_number works in the same fashion as in oracle. You need to give
to_number('<yourdata>','formatmask')
For example, to_number('1234567','FM9999999')
Regards,
Gautham.
----- Original Message -----
From: "Stéphane DEWITTE" <stephane@smeso.fr>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, April 17, 2002 6:31 PM
Subject: Re: [SQL] Need some help with dates
>
>
> > -----Message d'origine-----
> > De : Gautham S. Rao [mailto:gautam.rao@tatainfotech.com]
> > Envoyé : mercredi 17 avril 2002 15:00
> > À : Stéphane DEWITTE; pgsql-sql@postgresql.org
> > Objet : Re: [SQL] Need some help with dates
> >
> >
> > Hi Stéphane,
> >
> > Use,
> > update mclis
> > set cli_date = to_date(temp.temp1,'dd/mm/yyyy')
> >
> > Regards,
> > Gautham.
> >
> > ----- Original Message -----
> > From: "Stéphane DEWITTE" <stephane@smeso.fr>
> > To: <pgsql-sql@postgresql.org>
> > Sent: Wednesday, April 17, 2002 6:03 PM
> > Subject: [SQL] Need some help with dates
>
> Thanks a lot. I knew it was existing in Orcale, but I haven't find any
> documentation on this function. I have another problem of that style, I
must
> convert a varchar to a numeric. It seems that to_num or to_number doesn't
> exists, and cast tells me "Cannot cast type 'character varying' to
> 'numeric'". Does another function for that exists ?
> And where could I find a list of existing functions in Postgres 7.2 ?
>
> Regards,
> Stéphane.
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
> Hi Stéphane,
>
> to_number works in the same fashion as in oracle. You need to give
> to_number('<yourdata>','formatmask')
> For example, to_number('1234567','FM9999999')
>
> Regards,
> Gautham.
Thanks a lot ! That works !!
> Hi.
>
> First, let me apologize for my poor level in english, I hope you'll
> understand me.
>
> I'm new in PL/PgSQL, and I have a little problem.
>
> I have a temporary table (temp) with only varchar. I must do a request
> that fill another table (mclis) with temp's data. But on the new
> tables, I have a field in date format. When I make a
>
> update mclis
> set cli_date = temp.temp1
>
> he doesn't want because of the different formats ('column "cli_date" is
> of type 'date' but expression is of type 'character varying').
>
> In temp, my data are like 01/01/2001
>
Convert your date from varchar to date, using the to_date function.
ex:
to_date('05 Dec 2000', 'DD Mon YYYY')
Take a look at the formating functions available in Postgresql:
http://www.postgresql.org/idocs/index.php?functions-formatting.html
Regards
Miguel Carvalho
Stephane,
> > to_number works in the same fashion as in oracle. You need to give
> > to_number('<yourdata>','formatmask')
> > For example, to_number('1234567','FM9999999')
> >
> > Regards,
> > Gautham.
A list of PostgreSQL functions:
http://www7.us.postgresql.org/users-lounge/docs/7.2/postgres/functions.html
-Josh