Hehe, here is my tests with this:
ctntest2=# SELECT create_date FROM users; create_date
------------------------2000-08-29 13:01:53-042000-08-27 20:04:41-042000-08-27 21:24:28-042000-08-30
09:51:16-042000-07-2523:14:08-042000-07-25 23:14:08-042000-09-01 02:53:02-042000-07-25 23:14:08-04
(8 rows)
ctntest2=# SELECT substr(create_date,1,10) FROM users; substr
------------2000-08-292000-08-272000-08-272000-08-302000-07-252000-07-252000-09-012000-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-042000-08-27 20:04:41-042000-08-27 21:24:28-042000-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/ ------------