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
> [EMAIL PROTECTED] | [EMAIL PROTECTED]
> 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 [EMAIL PROTECTED] ---------
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
---------- http://www.comptechnews.com/~reaster/ ------------
