pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Robert B. Easter <reaster@comptechnews.com>
To: Jeff MacDonald <jeff@hub.org>
To: pgsql-sql@postgresql.org
Subject: Re: substring ..
Date: Tue, 19 Dec 2000 16:31:42 -0500
Message-ID: <00121916314203.02217@comptechnews> (raw)
In-Reply-To: <Pine.BSF.4.21.0012191505370.3386-100000@rage.hub.org>
References: <Pine.BSF.4.21.0012191505370.3386-100000@rage.hub.org>

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/ ------------



view thread (22+ messages)  latest in thread

Message-ID: <00121916314203.02217@comptechnews>
Permalink:  ../00121916314203.02217@comptechnews/
Also on:    postgresql.org/message-id/00121916314203.02217@comptechnews

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: reaster@comptechnews.com, jeff@hub.org
  Subject: Re: substring ..
  In-Reply-To: <00121916314203.02217@comptechnews>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox