Oracle TRUNC Function


/ Published in: SQL
Save to your folder(s)

A few examples of the Oracle TRUNC function.


Copy this code and paste it in your HTML
  1. DROP TABLE datelist;
  2.  
  3. CREATE TABLE datelist (
  4. datevalue DATE,
  5. timestampvalue TIMESTAMP
  6. );
  7.  
  8. INSERT INTO datelist (datevalue, timestampvalue) VALUES (SYSDATE, SYSTIMESTAMP);
  9.  
  10. SELECT datevalue, timestampvalue FROM datelist;
  11.  
  12. --Example 1
  13. SELECT TRUNC(datevalue, 'YEAR') AS TRUNCVALUE
  14. FROM datelist;
  15.  
  16. --Example 2
  17. SELECT TRUNC(datevalue, 'MONTH') AS TRUNCVALUE
  18. FROM datelist;
  19.  
  20. --Example 3
  21. SELECT TRUNC(datevalue, 'DD') AS TRUNCVALUE
  22. FROM datelist;
  23.  
  24. --Example 4
  25. SELECT TRUNC(datevalue, 'IW') AS TRUNCVALUE
  26. FROM datelist;
  27.  
  28. --Example 5
  29. SELECT TRUNC(SYSDATE) AS TRUNCVALUE
  30. FROM dual;
  31.  
  32. --Example 6
  33. SELECT TRUNC(timestampvalue) AS TRUNCVALUE
  34. FROM datelist;
  35.  
  36. --Example 7
  37. SELECT TRUNC(timestampvalue, 'MM') AS TRUNCVALUE
  38. FROM datelist;
  39.  
  40. --Example 8
  41. SELECT TRUNC(5718.46987) AS TRUNCVALUE
  42. FROM dual;
  43.  
  44. --Example 9
  45. SELECT TRUNC(5718.46987, 2) AS TRUNCVALUE
  46. FROM dual;
  47.  
  48. --Example 10
  49. SELECT TRUNC(5718.46987, -3) AS TRUNCVALUE
  50. FROM dual;
  51.  
  52. --Example 11
  53. SELECT TRUNC(5718.46987, 0) AS TRUNCVALUE
  54. FROM dual;

URL: http://www.databasestar.com/oracle-trunc/

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.