Thread: Fetching as Date from a String
hi, I am using Postgresql-7.1 with RedHat Linux-7.1 and JDK-1.3.1. I am having a table named 'inv_table' with a field 'inv_date' of datatype 'varchar'. But, the field contains the records in the format '05-10-02(dd-MM-yy)'. I created a view to take the field like: 'select inv_date::text::date from inv_table'. It fetches the date field in the format '2002-05-10(yyyy-MM-dd)'. ie, 'dd' part of the string is taken as the 'MM' here and vice versa. But, this fetching works fine for dates above '12-10-2002'. Is there a way to specify the format of the string in the 'Select' query itself. Please help me. thanks. bhuvana. __________________________________________________ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com
Hi, The default data processing can be a bit confusing. It depend on the settings of "set datestyle", etc... For ultimate control, lookup to_date and to_char in the help. There you can specify the exactly format to use. Hope this helps, On Wed, Oct 16, 2002 at 09:22:38PM -0700, Thirumoorthy Bhuvneswari wrote: > hi, > I am using Postgresql-7.1 with RedHat Linux-7.1 and > JDK-1.3.1. I am having a table named 'inv_table' with > a field 'inv_date' of datatype 'varchar'. But, the > field contains the records in the format > '05-10-02(dd-MM-yy)'. I created a view to take the > field like: > 'select inv_date::text::date from inv_table'. It > fetches the date field in the format > '2002-05-10(yyyy-MM-dd)'. ie, 'dd' part of the string > is taken as the 'MM' here and vice versa. But, this > fetching works fine for dates above '12-10-2002'. > Is there a way to specify the format of the string in > the 'Select' query itself. Please help me. thanks. > > bhuvana. > > __________________________________________________ > Do you Yahoo!? > Faith Hill - Exclusive Performances, Videos & More > http://faith.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
hi, Can u please give me an example for how to use 'to_char' or 'to_date' in the query. thanks. bhuvana. --- Martijn van Oosterhout <kleptog@svana.org> wrote: > Hi, > > The default data processing can be a bit confusing. > It depend on the > settings of "set datestyle", etc... > > For ultimate control, lookup to_date and to_char in > the help. There you can > specify the exactly format to use. > > Hope this helps, > > On Wed, Oct 16, 2002 at 09:22:38PM -0700, > Thirumoorthy Bhuvneswari wrote: > > hi, > > I am using Postgresql-7.1 with RedHat Linux-7.1 > and > > JDK-1.3.1. I am having a table named 'inv_table' > with > > a field 'inv_date' of datatype 'varchar'. But, the > > field contains the records in the format > > '05-10-02(dd-MM-yy)'. I created a view to take the > > field like: > > 'select inv_date::text::date from inv_table'. It > > fetches the date field in the format > > '2002-05-10(yyyy-MM-dd)'. ie, 'dd' part of the > string > > is taken as the 'MM' here and vice versa. But, > this > > fetching works fine for dates above '12-10-2002'. > > Is there a way to specify the format of the string > in > > the 'Select' query itself. Please help me. thanks. > > > > bhuvana. > > > > __________________________________________________ > > Do you Yahoo!? > > Faith Hill - Exclusive Performances, Videos & More > > http://faith.yahoo.com > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > There are 10 kinds of people in the world, those > that can do binary > > arithmetic and those that can't. __________________________________________________ Do you Yahoo!? Faith Hill - Exclusive Performances, Videos & More http://faith.yahoo.com
Look here, lots of examples: http://developer.postgresql.org/docs/postgres/functions-formatting.html On Wed, Oct 16, 2002 at 10:21:57PM -0700, Thirumoorthy Bhuvneswari wrote: > hi, > Can u please give me an example for how to use > 'to_char' or 'to_date' in the query. thanks. > > bhuvana. > --- Martijn van Oosterhout <kleptog@svana.org> wrote: > > Hi, > > > > The default data processing can be a bit confusing. > > It depend on the > > settings of "set datestyle", etc... > > > > For ultimate control, lookup to_date and to_char in > > the help. There you can > > specify the exactly format to use. > > > > Hope this helps, > > > > On Wed, Oct 16, 2002 at 09:22:38PM -0700, > > Thirumoorthy Bhuvneswari wrote: > > > hi, > > > I am using Postgresql-7.1 with RedHat Linux-7.1 > > and > > > JDK-1.3.1. I am having a table named 'inv_table' > > with > > > a field 'inv_date' of datatype 'varchar'. But, the > > > field contains the records in the format > > > '05-10-02(dd-MM-yy)'. I created a view to take the > > > field like: > > > 'select inv_date::text::date from inv_table'. It > > > fetches the date field in the format > > > '2002-05-10(yyyy-MM-dd)'. ie, 'dd' part of the > > string > > > is taken as the 'MM' here and vice versa. But, > > this > > > fetching works fine for dates above '12-10-2002'. > > > Is there a way to specify the format of the string > > in > > > the 'Select' query itself. Please help me. thanks. > > > > > > bhuvana. > > > > > > __________________________________________________ > > > Do you Yahoo!? > > > Faith Hill - Exclusive Performances, Videos & More > > > http://faith.yahoo.com > > > > > > ---------------------------(end of > > broadcast)--------------------------- > > > TIP 5: Have you checked our extensive FAQ? > > > > > > > > http://www.postgresql.org/users-lounge/docs/faq.html > > > > -- > > Martijn van Oosterhout <kleptog@svana.org> > > http://svana.org/kleptog/ > > > There are 10 kinds of people in the world, those > > that can do binary > > > arithmetic and those that can't. > > > __________________________________________________ > Do you Yahoo!? > Faith Hill - Exclusive Performances, Videos & More > http://faith.yahoo.com > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.