Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / PL SQL Data Types

 

TRUNC and ROUND

TRUNC (value,'W') is the easiest way of getting the first day of the week. TRUNC, typically applied to numbers, also work with dates and timestamps. TRUNC truncates the date to some level of precision. v_dt:= TRUNC (DATE|TIMESTAMP|INTERVAL value[,PRECISION]); Rather than numeric precision, here you use date format masks. For example, 'YYYY' truncates the date to years, and 'MM' to a month. If you don't specify any precision, the default is day ('DD'). You can't specify a combination of format masks. Only one can be specified at a time. SQL> SQL> declare   2      v_dt DATE;   3      v_form_tx VARCHAR2(25):='YYYY-MM-DD HH24:MI:SS';   4  begin   5      v_dt:=trunc(sysdate);   6      DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));   7      v_dt:=trunc(sysdate,'YYYY');   8      DBMS_OUTPUT.put_line(to_char(v_dt,v_form_tx));   9  end;  10  / 2007-06-09 00:00:00 2007-01-01 00:00:00 PL/SQL procedure successfully completed. SQL>