{"id":13346,"date":"2019-12-15T13:48:04","date_gmt":"2019-12-15T13:48:04","guid":{"rendered":"https:\/\/ittutorial.org\/?p=13346"},"modified":"2019-12-15T13:58:52","modified_gmt":"2019-12-15T13:58:52","slug":"oracle-introduction-to-sql-tutorial-5","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-introduction-to-sql-tutorial-5\/","title":{"rendered":"Oracle Introduction to SQL Tutorial \u2013 5"},"content":{"rendered":"

Hi everyone, in this tutorial we will look the Date functions.\u00a0 Date functions in SQL Language are given below.<\/p>\n

 <\/p>\n

\"\"<\/p>\n

 <\/p>\n

ADD_MONTHS Function<\/h3>\n

Used to add months to the date value that you type as a result of the SQL query<\/p>\n

Example:<\/strong><\/p>\n

select * from student where surname='Federer';<\/pre>\n
\"\"<\/p>\n
select first_name,surname,Add_Months(birth_day,1)from student where surname='Federer';<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Months_Between Functions<\/h3>\n
Used to find the month difference between two dates.<\/p>\n
Example:<\/strong><\/p>\n
select months_between('08-04-2019','08-01-2019') \"Between Months\" from dual;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Next_Day Function<\/h3>\n
It adds the date of the week that you give to the date value that you give in SQL Query and gives the corresponding date information.<\/p>\n
Note: The language definition of the day of the week may vary according to the “NLS_LANGUAGE<\/strong>” parameter.<\/span><\/span><\/p>\n
select next_day('20-Jan-2019','Monday') as \"Next Monday\" from dual;<\/pre>\n
\"\"<\/p>\n
 <\/p>\n
Round Function<\/h3>\n
It is used to round the value you give in the SQL Query to minute \/ hour \/ day \/ week \/ month.<\/p>\n
    \n
  • DD: Day rounding.<\/span><\/li>\n
  • W:\u00a0\u00a0 Week rounding.<\/span><\/li>\n
  • MM: Month rounding.<\/span><\/li>\n
  • HH: Hour rounding.<\/span><\/li>\n
  • MI: M\u0131nute rounding.<\/span><\/li>\n<\/ul>\n
    Example:<\/strong><\/p>\n
    select round (to_date(’18-May-2019′),’MM’) as “Month Rounding” from dual;<\/p>\n
    \"\"<\/p>\n
    Rounded May to June as shown in the picture.<\/p>\n
     <\/p>\n
    Trunc Function<\/h3>\n
    Rounds the reference date value that we provide in the SQL Query according to the rounding degree value that we will give in our query.<\/p>\n
    Example:<\/strong><\/p>\n
    select TRUNC( to_date('20-May-2019'),'W') as \"Trunc Week\" from dual;<\/pre>\n
    \"\"<\/p>\n
     <\/p>\n
    To_char with Dates<\/h3>\n
    When using the date format,<\/p>\n
      \n
    • Must be single-quoted and case-sensitive.<\/li>\n
    • It must contain the current date format.<\/li>\n
    • The format component is separated by a comma from the date value.<\/li>\n<\/ul>\n
      Example:<\/strong><\/p>\n
      select first_name, to_char(birth_day, 'DD Month YYYY') BIRTH_DAY from Student;<\/pre>\n
      \"\"<\/p>\n
       <\/p>\n
      Nvl Function<\/h3>\n
      Used to convert values from null to another value in SQL Query result.<\/p>\n
      Example:<\/strong><\/p>\n
      select * from student;<\/pre>\n
      \"\"<\/p>\n
       <\/p>\n
      Select first_name,surname,NVL(CASH,0) from student;<\/pre>\n
      \"\"<\/p>\n
      AVG Function<\/h3>\n
      It is used to average the given values.<\/p>\n
      Example:<\/strong><\/p>\n
      select first_name,last_name,salary from hr.employees;<\/pre>\n
      \"\"<\/p>\n
       <\/p>\n
      select avg(salary) from HR.employees;\r\n\r\n<\/pre>\n
      \"\"<\/p>\n
       <\/p>\n
      Count Function<\/h3>\n
      Used to find the number of rows in a table.<\/p>\n
      Example:<\/strong><\/p>\n
      select first_name from hr.employees;<\/pre>\n
      \"\"<\/p>\n
      There’s more than 25 lines in the image. Let’s see how many records there are<\/p>\n
      select count(*) from hr.employees;<\/pre>\n
      \"\"<\/p>\n
      Max\/Min Function<\/h3>\n
      Used to find the maximum \/ smallest equivalents of the given values.<\/p>\n
      Example: <\/strong><\/p>\n
      select max(salary) \"Max Salary\" from HR.employees;<\/pre>\n
      \"\"<\/p>\n
      select min(salary) \"Min Salary\" from HR.employees;\r\n\r\n<\/pre>\n
      \"\"<\/p>\n
       <\/p>\n
      Sum Function<\/h3>\n
      Used to find the sum of the values in the entered column.<\/p>\n
      Example:<\/strong><\/p>\n
      select SUM(salary) \"Sum Salary\" from HR.employees;<\/pre>\n
      \"\"<\/p>\n
       <\/p>\n
      See you in the next article..<\/p>\n","protected":false},"excerpt":{"rendered":"
      Hi everyone, in this tutorial we will look the Date functions.\u00a0 Date functions in SQL Language are given below.     ADD_MONTHS Function Used to add months to the date value that you type as a result of the SQL query Example: select * from student where surname=’Federer’; select first_name,surname,Add_Months(birth_day,1)from student where surname=’Federer’;   Months_Between …<\/p>\n","protected":false},"author":67,"featured_media":12330,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3019],"tags":[4936,4929,4875,4883,4873,4869,4910,1340,4870,4895,4905,4935,4926,4915,4921,4919,4920,4918,4917,3192,4872,4916,4928,4876,4912,4881,4880,4890,4867,3062,4879,4891,4923,4924,4922,4925,4909,4911,4877,4896,4908,4882,4884,4897,4901,4900,4898,4902,4903,4899,4871,3015,3018,4868,4904,4906,4893,4930,4934,4914,4933,4885,4887,4913,3000,4931,4889,4927,4907,4888,4886,4892,4874,4878,4894,4932],"class_list":["post-13346","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-case-insensetive","tag-create-view","tag-database-nedir","tag-insert-nedir","tag-kolay-sql","tag-mysql-sql","tag-or","tag-oracle-sql","tag-php-sql","tag-plsql","tag-right-outer-join","tag-selecting-multiple-colum","tag-sql-all-drop","tag-sql-all-statements","tag-sql-alter","tag-sql-alter-colum","tag-sql-alter-database","tag-sql-alter-statement","tag-sql-alter-table","tag-sql-and","tag-sql-baslangic-dersleri","tag-sql-creat-table","tag-sql-create-view","tag-sql-database","tag-sql-date-functions","tag-sql-dcl","tag-sql-ddl","tag-sql-delete","tag-sql-dersleri","tag-sql-distinct","tag-sql-dml","tag-sql-dml-nedir","tag-sql-drop-colum","tag-sql-drop-database","tag-sql-drop-statement","tag-sql-drop-table","tag-sql-filtering-data","tag-sql-functions","tag-sql-ifadeleri","tag-sql-ile-ne-yapilir","tag-sql-in-statement","tag-sql-insert","tag-sql-insert-nasil-yapilir","tag-sql-is-ilanlari","tag-sql-join-yapisi","tag-sql-kaynaklari","tag-sql-kullanmak","tag-sql-lef-join","tag-sql-left-outer-join","tag-sql-makaleleri","tag-sql-nasil-ogrenilir","tag-sql-nedir","tag-sql-ogrenme","tag-sql-ogrenmek","tag-sql-right-join","tag-sql-right-outer-join","tag-sql-select-islemi","tag-sql-set-auto-increment","tag-sql-sorting-results","tag-sql-subqueries","tag-sql-syntaxt-rule","tag-sql-tablo-olusturma","tag-sql-tablo-yapisi","tag-sql-to_char-function","tag-sql-tutorial","tag-sql-union-statement","tag-sql-update-islemi","tag-sql-view","tag-sql-where-statement","tag-sql-yapisi","tag-tablo-nedr","tag-tablodan-veri-cekmek","tag-temel-seviye-sql","tag-temel-seviyede-sql","tag-tsql","tag-union-table"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/sql.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/13346","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/67"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=13346"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/13346\/revisions"}],"predecessor-version":[{"id":13397,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/13346\/revisions\/13397"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/12330"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=13346"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=13346"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=13346"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}