Re: EXTRACT versur TO_CHAR

On Feb 28, 9:51 pm, "Dereck L. Dietz" <diet..._at_ameritech.net> wrote:
> Is there any performance differences between using:
>
> EXTRACT(MONTH FROM variable) and TO_CHAR(variable,'MM')
>
> or do they both perform just as efficiently as the other?
>
> I have some PL/SQL code where it is using the EXTRACT to compare date
> portions of one field against date portions of another field.
>
> Thanks.

The EXTRACT function was provided to support the new with 9i TIMESTAMP data types. It has limited use on traditional DATE data types being limited to extracting Year, Month, and Day only. So I would use to_char on DATE datatypes and on TIMESTAMP data types where you want to reformat the output and I would use EXTRACT where only a specific portion of the data is needed.

I have not ran performance tests but I suspect that you will need large row counts to be able to measure the difference. Perhaps someone has tested and saved the results and will share them with us.

HTH -- Mark D Powell -- Received on Thu Mar 01 2007 - 09:29:01 CST