Re: Datatype conversion help

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(at)postgresql(dot)org
[mailto:pgsql-sql-owner(at)postgresql(dot)org]Im Auftrag von Dmitry Tkach
Gesendet: Donnerstag, 10. Juli 2003 00:52
An: Yasir Malik
Cc: pgsql-sql(at)postgresql(dot)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(at)openratings(dot)com>
>>To: Yasir Malik <ymalik(at)cs(dot)stevens-tech(dot)edu>
>>Cc: pgsql-sql(at)postgresql(dot)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(at)bowmansystems(dot)com>
>>>>To: Yasir Malik <ymalik(at)cs(dot)stevens-tech(dot)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