Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

18 octobre 2005 2 18 /10 /octobre /2005 00:00

ON NUMBERS

This function allows to make two types of action on a number: 

- Keep the number of wished decimals (precision > = 0) 
- Centre the whole part of a number in a range (precision < 0) 

Example of preservation of the decimal part

<pre>SQL> select
  2   trunc(12.98764, 5) "+5 dec."
  3  ,trunc(12.98764, 4) "+4 dec."
  4  ,trunc(12.98764, 3) "+3 dec."
  5  ,trunc(12.98764, 2) "+2 dec."
  6  ,trunc(12.98764, 1) "+1 dec."
  7  ,trunc(12.98764, 0) "+0 dec."
  8   from dual ;

   +5 dec.    +4 dec.    +3 dec.    +2 dec.    +1 dec.    +0 dec.
---------- ---------- ---------- ---------- ---------- ----------
  12,98764    12,9876     12,987      12,98       12,9         12

SQL></pre>


Example of centring by ranges

<pre>SQL> select
  2   trunc(1234567,-6) "million"
  3  ,trunc(1234567,-5) "cent-mille"
  4  ,trunc(1234567,-4) "dix-mille"
  5  ,trunc(1234567,-3) "mille"
  6  ,trunc(1234567,-2) "cent"
  7  ,trunc(1234567,-1) "dix"
  8  from dual ;

   million cent-mille  dix-mille      mille       cent        dix
---------- ---------- ---------- ---------- ---------- ----------
   1000000    1200000    1230000    1234000    1234500    1234560

SQL></pre>


Extraction of the entire and decimal parts of a number

<pre>SQL> DECLARE
  2    LN$Num number := 100.95 ;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE( 'Entire part  : ' || To_char( TRUNC ( LN$Num ) )) ;
  5    DBMS_OUTPUT.PUT_LINE( 'Decimal part : ' || To_char( LN$Num - TRUNC (LN$Num ) ) ) ;
  6  END ;
  7  /
Entire part  : 100
Decimal part : ,95

Procédure PL/SQL terminée avec succès.

SQL></pre>



ON DATES

<pre>SQL>
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;

Session modifiée.

SQL>
SQL> -- Current date --
SQL> SELECT SYSDATE "Current date" FROM DUAL ;

Curent date
-------------------                                                             
08/10/2004 14:08:48                                                             

SQL>
SQL> -- date truncated to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;

'YEAR'                                                                         
-------------------                                                             
01/01/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;

'IYYY'                                                                         
-------------------                                                             
29/12/2003 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the quarter --
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;

'Q'                                                                             
-------------------                                                             
01/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;

'MONTH'                                                                         
-------------------                                                             
01/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the 1st day of the week --
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'                                                                           
-------------------                                                             
04/10/2004 00:00:00                                                             

SQL>
SQL> -- day of the week corresponding to the 1st day of the month --
SQL> SELECT TRUNC(SYSDATE, 'W')  "'W'" FROM DUAL;

'W'                                                                             
-------------------                                                             
08/10/2004 00:00:00                                                             

SQL>
SQL> -- day of the week corresponding to the 1st day of the year --
SQL> SELECT TRUNC(SYSDATE, 'WW')  "'WW'" FROM DUAL;

'WW'                                                                           
-------------------                                                             
07/10/2004 00:00:00                                                             

SQL>
SQL> -- day of the week corresponding to the 1st day of the ISO year --
SQL> SELECT TRUNC(SYSDATE, 'IW')  "'IW'" FROM DUAL;

'IW'                                                                           
-------------------                                                             
04/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the day (suppress hours) --
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;

'DD'                                                                           
-------------------                                                             
08/10/2004 00:00:00                                                             

SQL>
SQL> -- date truncated to the hour (suppress minutes) --
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;

'HH'                                                                           
-------------------                                                             
08/10/2004 14:00:00                                                             

SQL>
SQL> -- date truncated to the minute (suppress seconds) --
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;

'MI'                                                                           
-------------------                                                             
08/10/2004 14:08:00                                                             

SQL></pre>



Francois

Partager cet article

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article

commentaires

Francois 19/10/2005 12:25

Yes Martin, the first day of the week is not the same for everybody !

Francois

Martin 19/10/2005 10:30

SQL> alter session set nls_territory='GERMANY';

Session altered.

SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'
--------
17.10.05

SQL> alter session set nls_territory='AMERICA';

Session altered.

SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;

'DAY'
---------
16-OCT-05