Karl Reitschuster's Tech Space

Hi reader,

i needed to convert the date fraction from a times tamp type variable; Usually the way is to convert the time stamp to string and then to convert it back to date; Hmmm ... . This looks not very elegant ;-)

L_Date := To_Date(To_Char(L_Tmstmp,
                                  'DDMMYYYY HH24MISS'),
                          'DDMMYYYY HH24MISS');

Uuups! very readable; I found another much more eye friendly way to do it;

L_Date := CAST(L_Tmstmp AS DATE);

 Casting a type to another compatible type is more known in ordinary program languages then in database context. The second code snippet is my favourite. But let us check the performance of both approaches:

SQL> SET TIMING ON
SQL> PROMPT using CAST to convert TIMSTAMTP TO DATE
using CAST to convert TIMSTAMTP TO DATE
SQL> DECLARE
  2      L_Date   DATE;
  3      L_Tmstmp TIMESTAMP;
  4  BEGIN
  5      FOR i IN 1 .. 10000000 LOOP
  6          L_Date := CAST(L_Tmstmp AS DATE);
  7      END LOOP;
  8  
  9  END;
 10  /
 
Executed in 0,218 seconds

SQL> PROMPT using to_char/to_date to convert TIMSTAMTP TO DATE
using to_char/to_date to convert TIMSTAMTP TO DATE
SQL> DECLARE
  2      L_Date   DATE;
  3      L_Tmstmp TIMESTAMP;
  4  BEGIN
  5      FOR i IN 1 .. 10000000 LOOP
  6          L_Date := To_Date(To_Char(L_Tmstmp,
  7                                    'DDMMYYYY HH24MISS'),
  8                            'DDMMYYYY HH24MISS');
  9      END LOOP;
 10  END;
 11  /
 
Executed in 0,218 seconds

I was surpirsed - no difference! hopefully the code compiler optimization did not play a game with us;

/Karl