Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to get decimal to date form

how to get decimal to date form

From
wireless200@yahoo.com (wireless)
Date:
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

Re: how to get decimal to date form

From
Tomasz Myrta
Date:
> 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

Re: how to get decimal to date form

From
David Brown
Date:
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