X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 7ADA732A0BC for ; Wed, 20 Oct 2004 00:00:21 +0100 (BST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 55385-09 for ; Tue, 19 Oct 2004 23:00:09 +0000 (GMT) Received: from happy.onesystem.ca (happy.onesystem.ca [205.206.61.17]) by svr1.postgresql.org (Postfix) with ESMTP id 6C61F32A093 for ; Wed, 20 Oct 2004 00:00:10 +0100 (BST) Received: from elb_lx.onesystem.ca ([10.1.1.50]) by happy.onesystem.ca with asmtp (Exim 4.14) id 1CK2xc-0006FV-Ho; Tue, 19 Oct 2004 17:00:08 -0600 Message-ID: <41759C78.1040101@onesystem.com> Date: Tue, 19 Oct 2004 17:00:08 -0600 From: Edmund Bacon User-Agent: Mozilla Thunderbird 0.7.3 (X11/20040803) X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Re: help with to_date and to_char References: <416C74CC.6030309@dri.edu> <9E1E7300-2215-11D9-A5E5-000D93AE0944@sitening.com> In-Reply-To: <9E1E7300-2215-11D9-A5E5-000D93AE0944@sitening.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200410/132 X-Sequence-Number: 19235 Thomas F.O'Connell wrote: > There might be a better way, but this should do what you want. And I > think that you can safely replace '05' with when_month. > > select to_char( to_date( '05' || '/' || to_char( current_date, 'DD/YYYY' > ), 'MM/DD/YYYY' ), 'MON' ); > Perhaps select to_char(to_date('02', 'MM'), 'MON'); is better. When current_date is, say Aug 31 then select to_char( to_date('05' || '/' || to_char(current_date, 'DD/YYYY'), 'MM/DD/YYYY' ), 'MON' ); returns 'MAR', which is probably not what you want. This works with 7.3.2, 7.4.5 and 8.0beta2. > -tfo > > -- > Thomas F. O'Connell > Co-Founder, Information Architect > Sitening, LLC > http://www.sitening.com/ > 110 30th Avenue North, Suite 6 > Nashville, TN 37203-6320 > 615-260-0005 > > On Oct 12, 2004, at 7:20 PM, Lori wrote: > >> Im trying to do something very simple >> I have a field called when_month (integer ) >> so I want to get the month name for the integer >> >> this comes close to what I want >> update mytable set myfield=to_char(current_timestamp,'MON'); >> >> the result is myfield is set to OCT >> which is close to what I want but when I try to use a variable >> update mytable set myfield=to_char(when_month,'MON'); >> or a char with value of '05' or '5' >> update mytable set myfield=to_char('05','MON'); >> get error: >> update mytable set myfield=to_date(when_month,'MON'); >> update mytable set myfield=to_char('05','MON'); >> as well as >> update mytable set full_month= to_date('01/'05'/2004','DDMONYYYY'); >> all get errors >> >> Please can anyone help? >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 8: explain analyze is your friend > > > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend -- Edmund Bacon