From: listrec <listrec@epecon.de>
To: Dmitry Tkach <dmitry@openratings.com>
To: Yasir Malik <ymalik@cs.stevens-tech.edu>
Cc: pgsql-sql@postgresql.org
Subject: Re: Datatype conversion help
Date: Thu, 10 Jul 2003 07:58:28 +0200
Message-ID: <PCEJJIGCHIJCOOOGFAFEEEOJCAAA.listrec@epecon.de> (raw)
In-Reply-To: <3F0C9C84.5000808@openratings.com>
I tried
select
to_date(substring(to_char(yr,'0009'),2,4)||substring(to_char(mn,'09'),2,2)||
substring(to_char(dy,'09'),2,4),'YYYYMMDD');
which works fine....
Detlef
-----Ursprungliche Nachricht-----
Von: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]Im Auftrag von Dmitry Tkach
Gesendet: Donnerstag, 10. Juli 2003 00:52
An: Yasir Malik
Cc: pgsql-sql@postgresql.org
Betreff: Re: [SQL] Datatype conversion help
Yasir Malik wrote:
>I used trim and here's what I came up with:
>to_date(trim(to_char(yr, '9999') || trim(to_char(mn, '00')) ||
>trim(to_char(dy, '00'))), 'YYYYMMDD')
>
>Apparently to_char adds a space to the charecter you are casting.
>
>
I know :-)
And lpad doesn't - that's why I suggested it :-)
Dima
>
>On Wed, 9 Jul 2003, Dmitry Tkach wrote:
>
>
>
>>Date: Wed, 09 Jul 2003 18:40:37 -0400
>>From: Dmitry Tkach <dmitry@openratings.com>
>>To: Yasir Malik <ymalik@cs.stevens-tech.edu>
>>Cc: pgsql-sql@postgresql.org
>>Subject: Re: [SQL] Datatype conversion help
>>
>>What about lpad?
>>
>>select lpad (7, 2, 0) || '-' || lpad (9, 2, '0') || '-2003';
>> ?column?
>>------------
>> 07-09-2003
>>(1 row)
>>
>>
>>I hope, it helps...
>>
>>Dima
>>
>>Yasir Malik wrote:
>>
>>
>>
>>>Thank you so much! But my problem is that when I do
>>>to_char(mn, '00') || '-' || to_char(dy, '00') || '-' || to_char(yr,
>>>'9999')
>>>
>>>where mn, dy, and yr are ints, is that the output has a space after the
>>>the dash. For example, I get
>>>07- 25- 1994
>>>
>>>instead of what I want:
>>>07-25-1994
>>>
>>>Thanks,
>>>Yasir
>>>
>>>On Tue, 8 Jul 2003, Richard Rowell wrote:
>>>
>>>
>>>
>>>
>>>
>>>>Date: 08 Jul 2003 15:21:33 -0500
>>>>From: Richard Rowell <richard@bowmansystems.com>
>>>>To: Yasir Malik <ymalik@cs.stevens-tech.edu>
>>>>Subject: Re: [SQL] Datatype conversion help
>>>>
>>>>On Tue, 2003-07-08 at 15:07, Yasir Malik wrote:
>>>>
>>>>
>>>>
>>>>
>>>>>I've tried to_char(in_val, '99'), and that returns a string that is two
>>>>>
>>>>>
>>>>>
>>>>>
>>>>select to_char(9,'00');
>>>>
>>>>
>>>>
>>>>
>>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 8: explain analyze is your friend
>>>
>>>
>>>
>>>
>>
>>
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: listrec@epecon.de, dmitry@openratings.com, ymalik@cs.stevens-tech.edu
Subject: Re: Datatype conversion help
In-Reply-To: <PCEJJIGCHIJCOOOGFAFEEEOJCAAA.listrec@epecon.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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