Received: from comptechnews.com (cc993546-b.srst1.fl.home.com [24.3.77.52]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id eBJLPF623496 for ; Tue, 19 Dec 2000 16:25:15 -0500 (EST) (envelope-from reaster@comptechnews.com) Received: from comptechnews (reaster@localhost [127.0.0.1]) by comptechnews.com (8.10.2/8.10.2) with SMTP id eBJLVgZ07203; Tue, 19 Dec 2000 16:31:42 -0500 From: "Robert B. Easter" To: Jeff MacDonald , pgsql-sql@postgresql.org Subject: Re: substring .. Date: Tue, 19 Dec 2000 16:31:42 -0500 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="US-ASCII" References: In-Reply-To: MIME-Version: 1.0 Message-Id: <00121916314203.02217@comptechnews> Content-Transfer-Encoding: 8bit X-Archive-Number: 200012/229 X-Sequence-Number: 566 Hehe, here is my tests with this: ctntest2=# SELECT create_date FROM users; create_date ------------------------ 2000-08-29 13:01:53-04 2000-08-27 20:04:41-04 2000-08-27 21:24:28-04 2000-08-30 09:51:16-04 2000-07-25 23:14:08-04 2000-07-25 23:14:08-04 2000-09-01 02:53:02-04 2000-07-25 23:14:08-04 (8 rows) ctntest2=# SELECT substr(create_date,1,10) FROM users; substr ------------ 2000-08-29 2000-08-27 2000-08-27 2000-08-30 2000-07-25 2000-07-25 2000-09-01 2000-07-25 (8 rows) ctntest2=# SELECT create_date FROM users WHERE substr(create_date,1,7) = '2000-08'; create_date ------------------------ 2000-08-29 13:01:53-04 2000-08-27 20:04:41-04 2000-08-27 21:24:28-04 2000-08-30 09:51:16-04 (4 rows) Seems to work fine. My fields are TIMESTAMP. If you really still have trouble, then try a cast: substr(datefoo::TEXT,1,10) ?? On Tuesday 19 December 2000 14:06, Jeff MacDonald wrote: > hi folks.. > > i want to do this to a datetime field.. > > select foo from table where substr(datefoo,1,11) = '2000-12-14'; > > it returns no results yet.. > > select substr(datefoo,1,11) does return some values that say > 2000-12-14 > > any clues ? > > Jeff MacDonald, > > ----------------------------------------------------- > PostgreSQL Inc | Hub.Org Networking Services > jeff@pgsql.com | jeff@hub.org > www.pgsql.com | www.hub.org > 1-902-542-0713 | 1-902-542-3657 > ----------------------------------------------------- > Facsimile : 1 902 542 5386 > IRC Nick : bignose > PGP Public Key : http://bignose.hub.org/public.txt -- -------- Robert B. Easter reaster@comptechnews.com --------- - CompTechNews Message Board http://www.comptechnews.com/ - - CompTechServ Tech Services http://www.comptechserv.com/ - ---------- http://www.comptechnews.com/~reaster/ ------------