Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by hub.org (8.10.1/8.10.1) with ESMTP id e6CGu7W77674 for ; Wed, 12 Jul 2000 12:56:07 -0400 (EDT) Received: by rice.edu via sendmail from stdin id (Debian Smail3.2.0.102) for pgsql-sql@postgresql.org; Wed, 12 Jul 2000 11:55:56 -0500 (CDT) Date: Wed, 12 Jul 2000 11:55:56 -0500 From: "Ross J. Reedstrom" To: sandis Cc: pgsql-sql@postgresql.org Subject: Re: date comparision ??? Message-ID: <20000712115556.D1487@rice.edu> Mail-Followup-To: sandis , pgsql-sql@postgresql.org References: <005a01bfe83e$4fc52600$d17b02c3@mediaparks.lv> <39663B6D.57B67664@ipinc.com> <00c301bfea4c$265ff460$d17b02c3@mediaparks.lv> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0i In-Reply-To: <00c301bfea4c$265ff460$d17b02c3@mediaparks.lv>; from sandis@mediaparks.lv on Mon, Jul 10, 2000 at 11:52:16AM +0300 X-Archive-Number: 200007/132 On Mon, Jul 10, 2000 at 11:52:16AM +0300, sandis wrote: > Thanks for your input. Unfortunately, it doesn't helped.. > > Here is the samples. > > This query works fine: > SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime > '2000-06-02 06:11:01-07') = '2000' LIMIT 1; > datums_ > ---------------------- > 2000-07-06 18:51:27+03 > (1 row) > > But this fails, obviously because the function doesnt know the value of > datums_: > SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year',datetime > 'datums_') = '2000' LIMIT 1; > ERROR: Bad datetime external representation 'datums_' > > datums_ is a timestamp field. How do i get it's value to use in date_part() > function?? > date_part('year',datums_) and date_part('year',datetime datums_) doesnt > work also! Right, that's the syntax for a datetime literal, not a cast. How about: SELECT datums_ FROM jaunumi WHERE flag = 'a' AND date_part('year', datetime(datums_)) = 2000 LIMIT 1; By the way, what version are you using? The functional cast is needed for 6.5.X, but not for 7.X. Ross -- Ross J. Reedstrom, Ph.D., NSBRI Research Scientist/Programmer Computer and Information Technology Institute Rice University, 6100 S. Main St., Houston, TX 77005