From: David Brown <wireless200@yahoo.com>
To: Tomasz Myrta <jasiek@klaster.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to get decimal to date form
Date: Thu, 18 Sep 2003 12:14:53 -0700 (PDT)
Message-ID: <20030918191453.54926.qmail@web10506.mail.yahoo.com> (raw)
In-Reply-To: <3F68C302.9020602@klaster.net>
Okay thanks, this is how I ended up doing it:
TO_DATE(SUBSTR(TO_CHAR(rec_num,99999999999),1,6),'0YMMDD') AS Date
Another question though...
I have a field that is of type numeric so when I want to divide it like this:
SUM(vc_elapsed_time)/60.0
postgre complains "Unable to identify an operator '/' for types 'numeric' and 'double precision' You will have to retype this query using an explicit cast".
Is the best way to resolve this to cast both numerator and denominator as float?
CAST(SUM(vc_elapsed_time) AS FLOAT)/(CAST(60.0 AS FLOAT))
This seems to work but noticeably slows down the query.
-David
Tomasz Myrta <jasiek@klaster.net> wrote:
> In our postgre database is a decimal field with format YYMMDDhhmmss.9999999999
> where the 9s are random digits. I'm trying to strip off just the
> YYMMDD and put it in date form.
>
> So far I came up with:
> SUBSTR(TO_CHAR(rec_num,99999999999),1,6) AS Date which returns YMMDD.
>
> For example where the rec_num is 30608124143.47069519725 the above
> functions return 30608.
>
> I tried wrapping another TO_CHAR around it to try to format it to a
> date but this seems like it's a bit much for this purpose.
>
> Any suggestions would be appreciated.
>
> -David
1. replace 0 with 9 to get leading zeroes - 030608 instead of 30608
2. to_date('030608','YYMMDD');
Regards,
Tomasz Myrta
---------------------------------
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
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: wireless200@yahoo.com, jasiek@klaster.net
Subject: Re: how to get decimal to date form
In-Reply-To: <20030918191453.54926.qmail@web10506.mail.yahoo.com>
* 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