Обсуждение: Datatype conversion help
Suppose I have an integer between 0 and 99 and I want to covert it to string, and pad leading zeros if neccessary. For example, 1 => 01 10 => 10 I've tried to_char(in_val, '99'), and that returns a string that is two charecters, but there isn't a leading zero incase I have the number 2 as input. Any ideas? Thanks. Yasir
You want to use: nachbaur=# select to_char(5, '00');to_char --------- 05 (1 row) By using "0", you indicate you want leading zeros. See http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html for more information. On Tuesday 08 July 2003 01:07 pm, Yasir Malik wrote: > Suppose I have an integer between 0 and 99 and I want to covert it to > string, and pad leading zeros if neccessary. For example, > 1 => 01 > 10 => 10 > > I've tried to_char(in_val, '99'), and that returns a string that is two > charecters, but there isn't a leading zero incase I have the number 2 as > input. Any ideas? Thanks. > Yasir > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings -- /* Michael A. Nachbaur <mike@nachbaur.com>* http://nachbaur.com/pgpkey.asc*/ "Oh no, not again."
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'); >
http://www.postgresql.org/docs/view.php?version=7.3&idoc=1&file=functions-formatting.html#FUNCTIONS-FORMATTING-DATETIMEMOD-TABLE See the pattern modifier "FM". From the docs: "FM prefix - fill mode (suppress padding blanks and zeroes)" On Tuesday 08 July 2003 01:28 pm, 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 -- /* Michael A. Nachbaur <mike@nachbaur.com>* http://nachbaur.com/pgpkey.asc*/ "He expanded his chest to make it totally clear that here was the sort of man you only dared to cross if you had a team of Sherpas with you. "
Yasir, If this is a date you're playing with, simply use: to_char( <whatever date>, 'MM-DD-YYYY' ) to get what you want. -------------------------- David Olbersen iGuard Engineer St. Bernard Software 11415 West Bernardo Court San Diego, CA 92127 1-858-676-2277 x2152 > -----Original Message----- > From: Yasir Malik [mailto:ymalik@cs.stevens-tech.edu] > Sent: Tuesday, July 08, 2003 1:29 PM > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Datatype conversion help > > > 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 >
Yes, Mr. Nachbaur helped me out. Thanks. I don't think I can do to_char(<whatever date>, 'MM-DD-YYYY) because the date fields are originally stored as separate integers in my schema (they have to be that way). I still can't understand why the extra space was added after the dash. It just made my life more miserable. Yasir On Tue, 8 Jul 2003, David Olbersen wrote: > Date: Tue, 8 Jul 2003 14:02:55 -0700 > From: David Olbersen <DOlbersen@stbernard.com> > To: pgsql-sql@postgresql.org > Subject: Re: [SQL] Datatype conversion help > > Yasir, > > If this is a date you're playing with, simply use: > > to_char( <whatever date>, 'MM-DD-YYYY' ) > > to get what you want. > > -------------------------- > David Olbersen > iGuard Engineer > St. Bernard Software > 11415 West Bernardo Court > San Diego, CA 92127 > 1-858-676-2277 x2152 > > > > -----Original Message----- > > From: Yasir Malik [mailto:ymalik@cs.stevens-tech.edu] > > Sent: Tuesday, July 08, 2003 1:29 PM > > To: pgsql-sql@postgresql.org > > Subject: Re: [SQL] Datatype conversion help > > > > > > 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 8: explain analyze is your friend >
On Tuesday 08 Jul 2003 10:19 pm, Yasir Malik wrote: > Yes, Mr. Nachbaur helped me out. Thanks. I don't think I can do > to_char(<whatever date>, 'MM-DD-YYYY) > because the date fields are originally stored as separate integers in my > schema (they have to be that way). I still can't understand why the extra > space was added after the dash. It just made my life more miserable. > Yasir You are not the only one it makes miserable. I believe it's there to provide compatibility with Oracle/some other db. Their function does it so ours should too. Very strange behaviour though. -- Richard Huxton
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 > >
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. Yasir 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 > > > > > >
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 >>> >>> >>> >>> >> >>
I will surely use your suggestion in my future programs. Thanks, Yasir On Wed, 9 Jul 2003, Dmitry Tkach wrote: > Date: Wed, 09 Jul 2003 18:51:48 -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 > > 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 >
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