Received: from smtp1.ihug.co.nz (tk1.ihug.co.nz [203.29.160.13]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA63415 for ; Thu, 2 Mar 2000 04:58:29 -0500 (EST) (envelope-from markir@ihug.co.nz) Received: from ihug.co.nz (IDENT:markir@p47-max3.wlg.ihug.co.nz [209.79.142.111]) by smtp1.ihug.co.nz (8.9.3/8.9.3/Debian/GNU) with ESMTP id WAA03131; Thu, 2 Mar 2000 22:58:10 +1300 Sender: markir@smtp1.ihug.co.nz Message-ID: <38BE3A6E.D18B24C9@ihug.co.nz> Date: Thu, 02 Mar 2000 22:54:54 +1300 From: Mark Kirkwood X-Mailer: Mozilla 4.61 [en] (X11; I; Linux 2.2.12-20 i686) X-Accept-Language: en MIME-Version: 1.0 To: forgacs@negral.com, pgsql-sql@postgreSQL.org, efte@negral.com Subject: RE: DATE Formating Problem Again References: <38B77942.8B5B6A85@ihug.co.nz> <26472.951552291@sss.pgh.pa.us> <38B8364F.FD8D1D3D@ihug.co.nz> <15360.951606889@sss.pgh.pa.us> <38B9B9E6.82914F15@ihug.co.nz> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit >I am searching after the date formatting solution, but didn't found a >correct one. > >So example ORACLE has > - to_char function to make any kind of date format from a date type: >to_char(now,'yyyy.mm.dd') > - to_date to makes a date type from any kind of string: to_date >('1999.11.23','yyyy.mm.dd') >(sniped...) I have wrestled a bit with this myself - being an Oracle dba ( but i'm alright now....), the date <-> char conversion is very different is Postgresql. However I have found that the "date_part" and "interval" functions seem to do the job ok, sometimes combined with the casting operator "::datetime". e.g : ( for release 7.0 ) date -> number db1=> select date_part('year',now()); date_part ----------- 2000 and char -> interval db1=> select interval('4 hours'::interval); ?column? ---------- 04:00 and char -> datetime via interval db1=> select ('1999-01-01 '||interval('4 hours'::interval))::datetime; ?column? ------------------------ 1999-01-01 04:00:00+13 I hope this helps a bit... Cheers Mark