Hi

I've a table bins in which I've a column named "Date In"&= nbsp; which stores values of "Date" type. I've postgresql 8.3.5

Now = when I execute query " select * from bins where "Date In" like '%'; " I get= the folowing error :

SQL error:

ERROR:  operator does not exist: date ~~ unknown
LINE 1: select * =
from bins where "Date In" like '%';
                                    =
       ^
HINT:  No operator matches the given name and argument type(s).=
 You might need to add explicit type casts.

=0A=09=09

In st= atement:
select * from bins where "Date In" like '%';

Please = help...I'm stuck for like 3-4 hours now on this X_X

Rohit

<= /div>
=0A

Add more friends to your messe= nger and enjoy! Invite them now. --0-1740481573-1239515917=:805-- From pavel.stehule@gmail.com Sun Apr 12 08:53:38 2009 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id CAD2C632568 for ; Sun, 12 Apr 2009 05:53:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 93947-07 for ; Sun, 12 Apr 2009 05:53:28 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f169.google.com (mail-bw0-f169.google.com [209.85.218.169]) by mail.postgresql.org (Postfix) with ESMTP id D4028633E31 for ; Sun, 12 Apr 2009 04:36:17 -0300 (ADT) Received: by bwz17 with SMTP id 17so3128067bwz.19 for ; Sun, 12 Apr 2009 00:36:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type :content-transfer-encoding; bh=8q0J1Er0LyPPDwWK50Fyr+CtD3r3Sho6KBFPePCJSgk=; b=xe4lM7o9rmkyTISS1jt0AQRCXnYpxZ8M0tvTRhJOtVuvmeJjbOC97HeZJb5McLF+8W asefzQ1WA4HxciAqhMWAD19nJuVp9P24ks7OPKeIFDzOpBlKjPkUQFGCA7QukXq5a/Oc vCUHPO3FtpvIPtvQADKD+P3pgqVHYTN9tgarw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; b=jYNCdn6a58SUUbYtCs7SOOkmjdqMtzw1ZijE1CrHNnjj821VMnm9SedZWYDBNk1cHp PJ6urDDkOemNcgykxJcjWX0K7/RWn9L5PV+DtNYNxbRvHCH8GzAgPcYKAAhxh0wXBtlO jBblDmBXIBf7aFb84ueFTbBRSUXkHNDZFXUWs= MIME-Version: 1.0 Received: by 10.223.120.197 with SMTP id e5mr1457549far.25.1239521776216; Sun, 12 Apr 2009 00:36:16 -0700 (PDT) In-Reply-To: <60074.805.qm@web33104.mail.mud.yahoo.com> References: <60074.805.qm@web33104.mail.mud.yahoo.com> Date: Sun, 12 Apr 2009 09:36:16 +0200 Message-ID: <162867790904120036o1d2ae20dn9fbdc0fc4640a06e@mail.gmail.com> Subject: Re: ERROR: operator does not exist: date ~~ unknown From: Pavel Stehule To: Rohit Suman Cc: pgsql-sql@postgresql.org Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/55 X-Sequence-Number: 32401 Hello it means - there are not operator LIKE for datatype date. Why do you do it? Try to look on date_part or extract functions. Fast solution, bot not optimal, is cast date to text via function to_char(date, 'mask') regards Pavel Stehule 2009/4/12 Rohit Suman : > Hi > > I've a table bins in which I've a column named "Date In"=C2=A0 which stor= es > values of "Date" type. I've postgresql 8.3.5 > > Now when I execute query " select * from bins where "Date In" like '%'; "= I > get the folowing error : > > > SQL error: > > ERROR: operator does not exist: date ~~ unknown > LINE 1: select * from bins where "Date In" like '%'; > ^ > HINT: No operator matches the given name and argument type(s). You might > need to add explicit type casts. > > In statement: > select * from bins where "Date In" like '%'; > > Please help...I'm stuck for like 3-4 hours now on this X_X > > Rohit > > ________________________________ > Add more friends to your messenger and enjoy! Invite them now. From handsome_94@yahoo.com Sun Apr 12 09:50:26 2009 Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 3083D632350 for ; Sun, 12 Apr 2009 06:50:26 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 23777-03 for ; Sun, 12 Apr 2009 06:50:19 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from web33107.mail.mud.yahoo.com (web33107.mail.mud.yahoo.com [209.191.69.137]) by mail.postgresql.org (Postfix) with SMTP id 5F02F6322D1 for ; Sun, 12 Apr 2009 06:50:24 -0300 (ADT) Received: (qmail 95095 invoked by uid 60001); 12 Apr 2009 09:50:22 -0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s1024; t=1239529822; bh=Yo/YIdToHNji1cki9TzK5K4mEApYExjwUyBzFdwDnk0=; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=K9iI/BlMqcELpUbAZvycbdyk4aHAw0jUPFARmq0U9seOKmBRHL2CBVMb+lA+yxB81Zi+B2OvtPvQJniUk/6o6WBlC9xeWgmGQcpiRXJjkp9Z+630U1vd4cRPs0j5PgKTEiCAiRTU/pRqrE2EaxzbDkvjaAFQM4ulm6Bq4bDU3yg= DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:X-YMail-OSG:Received:X-Mailer:Date:From:Subject:To:Cc:MIME-Version:Content-Type; b=iwwrTAp65tt1/cAQ9uhe0a62vJnl24qDvmIcfShPPV+P0zI9wzKHut/8IH3dYs68eCviECE9NlDSlCsI26VPSl832EJgx/W2Z90znflQTapeRnUZUKUiGPSUYBo9+V4gTFsg5MinsOgXwDnZXs04DSTvrLvQsC8kdsJhWO121dc=; Message-ID: <763646.95021.qm@web33107.mail.mud.yahoo.com> X-YMail-OSG: jng2VJYVM1l.oS1W5lW8J_1zkR.f9rox.5j2vdWgdWtKPsMmuBdAy53VHm1Bss7UIijQvUTGzf4cDh.B60Xj6fVTfbsiV_BKNFuS9ACsnY.5bjquDgS_evlyC5JislGm20uq4QLObXcWSpJsZr7PRqKeJc1CZ0AwclkgLgMVgbDF8QXYIj8mmaZo16EkcAj2aNpPYgV4o.EELsOlZjJ2NHhd5WJGS13BfNKCOTgKV1zyvo0wD4wx5DpSH3wIVxmj9d.P2J2T7rjJyE_T_T9mmdSl3j0kI0ZB03FmPs1UcPDeQKcTx1BzqwOBQcMUNffG75G8wCip4yeRAK_cdtIhOTbF02FNL4GTBrU6cOo- Received: from [59.180.34.254] by web33107.mail.mud.yahoo.com via HTTP; Sun, 12 Apr 2009 02:50:22 PDT X-Mailer: YahooMailRC/1277.35 YahooMailWebService/0.7.289.1 Date: Sun, 12 Apr 2009 02:50:22 -0700 (PDT) From: Rohit Suman Subject: Fw: ERROR: operator does not exist: date ~~ unknown To: pavel.stehule@gmail.com Cc: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-997265317-1239529822=:95021" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200904/56 X-Sequence-Number: 32402 --0-997265317-1239529822=:95021 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Ya...I simply changed column type to text and it worked. :D=0A=0AI had requ= irements where I needed such a functionality. There was no way to do it any= other way...=0A=0AThanks man=0A=0A=0A=0A----- Forwarded Message ----=0AFro= m: Pavel Stehule =0ATo: Rohit Suman =0ACc: pgsql-sql@postgresql.org=0ASent: Sunday, 12 April, 2009 1:0= 6:16 PM=0ASubject: Re: [SQL] ERROR: operator does not exist: date ~~ unknow= n=0A=0AHello=0A=0Ait means - there are not operator LIKE for datatype date.= Why do you=0Ado it? Try to look on date_part or extract functions.=0A=0AFa= st solution, bot not optimal, is cast date to text via function=0Ato_char(d= ate, 'mask')=0A=0Aregards=0APavel Stehule=0A=0A=0A2009/4/12 Rohit Suman :=0A> Hi=0A>=0A> I've a table bins in which I've a colu= mn named "Date In" which stores=0A> values of "Date" type. I've postgresql= 8.3.5=0A>=0A> Now when I execute query " select * from bins where "Date In= " like '%'; " I=0A> get the folowing error :=0A>=0A>=0A> SQL error:=0A>=0A>= ERROR: operator does not exist: date ~~ unknown=0A> LINE 1: select * from= bins where "Date In" like '%';=0A> = ^=0A> HINT: No operator matches the given name and argument type(s). Y= ou might=0A> need to add explicit type casts.=0A>=0A> In statement:=0A> sel= ect * from bins where "Date In" like '%';=0A>=0A> Please help...I'm stuck f= or like 3-4 hours now on this X_X=0A>=0A> Rohit=0A>=0A> ___________________= _____________=0A> Add more friends to your messenger and enjoy! Invite them= now.=0A=0A=0A=0A Get perfect Email ID for your Resume. Grab now http:= //in.promos.yahoo.com/address --0-997265317-1239529822=:95021 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable

Ya...I simply changed column type to text and it worked. :D

= I had requirements where I needed such a functionality. There was no way to= do it any other way...

Thanks man

----- Forwarded Message ----
From: Pavel Stehule <pavel.stehule@gmail.com>
To: Rohit Suman <handsome_94= @yahoo.com>
Cc: pgsq= l-sql@postgresql.org
Sent:<= /b> Sunday, 12 April, 2009 1:06:16 PM
Subject: Re: [SQL] ERROR: operator does not exist: date = ~~ unknown

Hello

it means - there are not operator LIK= E for datatype date. Why do you
do it? Try to look on date_part or extra= ct functions.

Fast solution, bot not optimal, is cast date to text v= ia function
to_char(date, 'mask')

regards
Pavel Stehule

2009/4/12 Rohit Suman <handsome_94@yahoo.com>:
>= ; Hi
>
> I've a table bins in which I've a column named "Date I= n" which stores
> values of "Date" type. I've postgresql 8.3.5<= br>>
> Now when I execute query " select * from bins where "Date I= n" like '%'; " I
> get the folowing error :
>
>
> S= QL error:
>
> ERROR: operator does not exist: date ~~ unk= nown
> LINE 1: select * from bins where "Date In" like '%';
> = &nbs= p; ^
> HINT: No operator matches the given name a= nd argument type(s). You might
> need to add explicit type casts.
= >
> In statement:
> select * from bins where "Date In" like = '%';
>
> Please help...I'm stuck for like 3-4 hours now on this= X_X
>
> Rohit
>
> ________________________________=
> Add more friends to your messenger and enjoy! Invite them now.
=

=0A
Get rid of Add-On= s in your email ID. Get yourname@rocketmail.com. Sign up now! --0-997265317-1239529822=:95021-- From tgl@sss.pgh.pa.us Sun Apr 12 15:16:53 2009 Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 96084632D5E for ; Sun, 12 Apr 2009 12:16:53 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 85443-04 for ; Sun, 12 Apr 2009 12:16:52 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by mail.postgresql.org (Postfix) with ESMTP id C49E7632B79 for ; Sun, 12 Apr 2009 12:16:51 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.2/8.14.2) with ESMTP id n3CFGojQ025844; Sun, 12 Apr 2009 11:16:50 -0400 (EDT) To: Rohit Suman cc: pavel.stehule@gmail.com, pgsql-sql@postgresql.org Subject: Re: Fw: ERROR: operator does not exist: date ~~ unknown In-reply-to: <763646.95021.qm@web33107.mail.mud.yahoo.com> References: <763646.95021.qm@web33107.mail.mud.yahoo.com> Comments: In-reply-to Rohit Suman message dated "Sun, 12 Apr 2009 02:50:22 -0700" Date: Sun, 12 Apr 2009 11:16:49 -0400 Message-ID: <25843.1239549409@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200904/57 X-Sequence-Number: 32403 Rohit Suman writes: > Ya...I simply changed column type to text and it worked. :D > I had requirements where I needed such a functionality. There was no way to do it any other way... You should really, really take a second look at those "requirements". Applying a textual pattern match to a date is a horrid idea for a number of reasons, most notably that it's just about certain to break if someone changes DateStyle. Think about what it is you really want to do (select a particular year, perhaps?) and see if there's not a datetime-based operation that does it. regards, tom lane