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