X-Original-To: pgsql-novice-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 722BF529D2
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 13:15:13 -0300 (ADT)
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 40332-10
	for <pgsql-novice-postgresql.org@localhost.postgresql.org>;
	Tue,  2 Aug 2005 16:15:02 +0000 (GMT)
Received: from mail.99main.com (mail.99main.com [208.28.184.11])
	by svr1.postgresql.org (Postfix) with ESMTP id 9EBCE528EF
	for <pgsql-novice@postgresql.org>; Tue,  2 Aug 2005 13:14:59 -0300 (ADT)
Received: from 99main.com (localhost [127.0.0.1])
	by mail.99main.com (8.12.8+Sun/8.12.8) with ESMTP id j72GEuHm002151;
	Tue, 2 Aug 2005 12:14:58 -0400 (EDT)
From: "Keith Worthington" <keithw@narrowpathinc.com>
To: "Walker, Jed S" <Jed_Walker@cable.comcast.com>
Cc: <pgsql-novice@postgresql.org>
Reply-To: KeithW@narrowpathinc.com
Subject: Re: Make year 01/01/0001 but leave timestamp alone
Date: Tue, 2 Aug 2005 12:14:56 -0400
Message-Id: <20050802161204.M17641@narrowpathinc.com>
In-Reply-To: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
References: 
 <4A59B6AA01F1874283EA66C976ED51FC4663BB@COENGEXCMB01.cable.comcast.com>
X-Mailer: Open WebMail 2.50 20050106
X-OriginatingIP: 68.235.22.242 (npkeithw)
MIME-Version: 1.0
Content-Type: text/plain;
	charset=iso-8859-1
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.009 tagged_above=0 required=5 tests=AWL
X-Spam-Level: 
X-Archive-Number: 200508/9
X-Sequence-Number: 14030

On Tue, 2 Aug 2005 09:49:19 -0600, Walker, Jed S wrote
> I have a situation where we have a timestamp column but in special cases
> we want to set the date to '01/01/0001' and leave the timestamp alone.
> For example, '11/04/2005 10:30:05' would become '01/01/0001 
> 10:30:05'. I've been going through the various date time functions 
> but don't see a simple way to do this. Can someone help?
> 
> Thanks,
> 
> Jed

Jed,

There may be a more elegant way to do it but this is the first thing that lept
to my mind.

SELECT '01/01/0001'::date,
       now()::time,
       (    '01/01/0001'::date::varchar
         || ' '
         || now()::time::varchar
       )::timestamp;

Kind Regards,
Keith