Thread: substring ..
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
Hi Jeff, '2000-12-14' is only 10 chars long. You're asking for an 11-char long substring to match a 10-char ... not going to happen! You can see this better if you do something like this ... select '@' || substr(datefoo,1,11) || '@' from table; ... and you'll get results like: @2000-12-14 @ So, you could modify your query to do: select foo from table where substr(datefoo, 1, 10) = '2000-12-14'; Alternatively, what's wrong with this approach? select foo from table where date(datefoo) = '2000-12-14'; I think that might execute a little faster. Hope this helps Francis Solomon > 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,
On Tue, 19 Dec 2000, 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 ? My guess is that it's a trailing space thing. '2000-12-14' is only 10 characters not 11... What you're actually getting is probably '2000-12-14 '
Jeff,
> 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
Well, for one it's not a string, it's a datetime field.
WHy are you trying to substring a datetime field, anyway?
-Josh
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 436-9166 for law firms, small
businesses fax 436-0137 and non-profit organizations. pager 338-4078 San
Francisco
> 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 Ummm... because '2000-12-14' is a ten-character, not eleven character long string. Try substr(datefoo,1,10) and it works for me (under 7.1devel). However, this all seems sloppy. Why not extract the date, and compare it as a date? -- Joel Burton, Director of Information Systems -*- jburton@scw.org Support Center of Washington (www.scw.org)
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 try remove the "-" from your WHERE clausule... This signal appears only in SELECT results... -- ====================================================== AKACIA TECNOLOGIA Desenvolvimento de sistemas para Internet www.akacia.com.br
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/ ------------
On Tue, 19 Dec 2000, 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';
And why not to_char()?
Karel
Hi,there, I am not sure what is your question mean. However, if the type of datefoo is a timestamp then try: select foo from table where date(datefoo) = '2000-12-14'; select foo from table where datefoo::date = '2000-12-14'::date; select foo from table where substr(datefoo,1,10) = '2000-12-14'; might work also. Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com On Tue, 19 Dec 2000, 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 >