(window.webpackJsonp=window.webpackJsonp||[]).push([[2340],{2749:function(t,s,a){"use strict";a.r(s);var e=a(31),n=Object(e.a)({},(function(){var t=this,s=t.$createElement,a=t._self._c||s;return a("ContentSlotsDistributor",{attrs:{"slot-key":t.$parent.slotKey}},[a("h1",{attrs:{id:"dates"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#dates"}},[t._v("#")]),t._v(" Dates")]),t._v(" "),a("h2",{attrs:{id:"date-arithmetic-difference-between-dates-in-days-hours-minutes-and-or-seconds"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#date-arithmetic-difference-between-dates-in-days-hours-minutes-and-or-seconds"}},[t._v("#")]),t._v(" Date Arithmetic - Difference between Dates in Days, Hours, Minutes and/or Seconds")]),t._v(" "),a("p",[t._v("In oracle, the difference (in days and/or fractions thereof) between two "),a("code",[t._v("DATE")]),t._v("s can be found using subtraction:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-03-23'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2015-12-25'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs the number of days between the two dates:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("DIFFERENCE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("----------")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("89")]),t._v("\n\n")])])]),a("p",[t._v("And:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-02 01:01:12'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-01 00:00:00'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" difference\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   DUAL\n\n")])])]),a("p",[t._v("Outputs the fraction of days between two dates:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("DIFFERENCE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("----------")]),t._v("\n    "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1.0425")]),t._v("\n\n")])])]),a("p",[t._v("The difference in hours, minutes or seconds can be found by multiplying this number by "),a("code",[t._v("24")]),t._v(", "),a("code",[t._v("24*60")]),t._v(" or "),a("code",[t._v("24*60*60")]),t._v(" respectively.")]),t._v(" "),a("p",[t._v("The previous example can be changed to get the days, hours, minutes and seconds between two dates using:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TRUNC"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" difference                       "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" days"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       TRUNC"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[t._v("MOD")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("24")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("       "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("24")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" hours"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       TRUNC"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[t._v("MOD")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("24")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("60")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("60")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" minutes"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       TRUNC"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token function"}},[t._v("MOD")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("24")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("60")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("60")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("60")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" seconds\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-02 01:01:12'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-01 00:00:00'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" difference\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   DUAL\n\n")])])]),a("p",[t._v(");")]),t._v(" "),a("p",[a("strong",[t._v("(Note: "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions201.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TRUNC()")]),a("OutboundLink")],1),t._v(" is used rather than "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions058.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("FLOOR()")]),a("OutboundLink")],1),t._v(" to correctly handle negative differences.)")])]),t._v(" "),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("DAYS HOURS MINUTES SECONDS\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---- ----- ------- -------")]),t._v("\n   "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("     "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("       "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("      "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("12")]),t._v("\n\n")])])]),a("p",[t._v("The previous example can also be solved by converting the numeric difference to an "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#i38598",target:"_blank",rel:"noopener noreferrer"}},[t._v("interval"),a("OutboundLink")],1),t._v(" using "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions103.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("NUMTODSINTERVAL()")]),a("OutboundLink")],1),t._v(":")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" days"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("HOUR")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" hours"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("MINUTE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" minutes"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SECOND")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" seconds\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" NUMTODSINTERVAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n           TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-02 01:01:12'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n             "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-01 00:00:00'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n           "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'DAY'")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" difference\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   DUAL\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("h2",{attrs:{id:"setting-the-default-date-format-model"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#setting-the-default-date-format-model"}},[t._v("#")]),t._v(" Setting the Default Date Format Model")]),t._v(" "),a("p",[t._v("When Oracle implicitly converts from a "),a("code",[t._v("DATE")]),t._v(" to a string or vice-versa (or when "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_CHAR()")]),a("OutboundLink")],1),t._v(" or "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_DATE()")]),a("OutboundLink")],1),t._v(" are explicitly called without a format model) the "),a("code",[t._v("NLS_DATE_FORMAT")]),t._v(" session parameter will be used as the format model in the conversion. If the literal does not match the format model then an exception will be raised.")]),t._v(" "),a("p",[t._v("You can review this parameter using:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" NLS_SESSION_PARAMETERS "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("WHERE")]),t._v(" PARAMETER "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'NLS_DATE_FORMAT'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("You can set this value within your current session using:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ALTER")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SESSION")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SET")]),t._v(" NLS_DATE_FORMAT "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[a("strong",[t._v("(Note: this does not change the value for any other users.)")])]),t._v(" "),a("p",[t._v("If you rely on the "),a("code",[t._v("NLS_DATE_FORMAT")]),t._v(" to provide the format mask in "),a("code",[t._v("TO_DATE()")]),t._v(" or "),a("code",[t._v("TO_CHAR()")]),t._v(" then you should not be surprised when your queries break if this value is ever changed.")]),t._v(" "),a("h2",{attrs:{id:"date-arithmetic-difference-between-dates-in-months-or-years"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#date-arithmetic-difference-between-dates-in-months-or-years"}},[t._v("#")]),t._v(" Date Arithmetic - Difference between Dates in Months or Years")]),t._v(" "),a("p",[t._v("The difference in months between two dates can be found using the "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions089.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("MONTHS_BETWEEN( date1, date2 )")]),a("OutboundLink")],1),t._v(":")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" MONTHS_BETWEEN"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-03-10'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2015-03-10'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("DIFFERENCE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("----------")]),t._v("\n        "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("12")]),t._v("\n\n")])])]),a("p",[t._v("If the difference includes part months then it will return the fraction of the month based on there being "),a("strong",[t._v("31")]),t._v(" days in each month:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" MONTHS_BETWEEN"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2015-02-15'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2015-01-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" difference "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("DIFFERENCE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("----------")]),t._v("\n "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1.4516129")]),t._v("\n\n")])])]),a("p",[t._v("Due to "),a("code",[t._v("MONTHS_BETWEEN")]),t._v(" assuming 31 days per month when there can be fewer days per month then this can result in different values for differences spanning the boundaries between months.")]),t._v(" "),a("p",[t._v("Example:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" MONTHS_BETWEEN"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-02-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-02-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTERVAL")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'1'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"JAN-FEB"')]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       MONTHS_BETWEEN"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-03-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-03-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTERVAL")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'1'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"FEB-MAR"')]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       MONTHS_BETWEEN"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-04-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-04-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTERVAL")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'1'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"MAR-APR"')]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       MONTHS_BETWEEN"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-05-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-05-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTERVAL")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'1'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v('"APR-MAY"')]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Output:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("JAN"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v("FEB FEB"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v("MAR MAR"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v("APR APR"),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v("MAY\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("------- ------- ------- -------")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.03226")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.09677")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.03226")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("0.06452")]),t._v("\n\n")])])]),a("p",[t._v("The difference in years can be found by dividing the month difference by 12.")]),t._v(" "),a("h2",{attrs:{id:"generating-dates-with-no-time-component"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#generating-dates-with-no-time-component"}},[t._v("#")]),t._v(" Generating Dates with No Time Component")]),t._v(" "),a("p",[t._v("All "),a("code",[t._v("DATE")]),t._v("s have a time component; however, it is customary to store dates which do not need to include time information with the hours/minutes/seconds set to zero (i.e. midnight).")]),t._v(" "),a("p",[t._v("Use an "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#BABGIGCJ",target:"_blank",rel:"noopener noreferrer"}},[t._v("ANSI "),a("code",[t._v("DATE")]),t._v(" literal"),a("OutboundLink")],1),t._v(" (using "),a("a",{attrs:{href:"https://en.wikipedia.org/wiki/ISO_8601#Calendar_dates",target:"_blank",rel:"noopener noreferrer"}},[t._v("ISO 8601 Date format"),a("OutboundLink")],1),t._v("):")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2000-01-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Convert it from a string literal using "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_DATE()")]),a("OutboundLink")],1),t._v(":")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2001-01-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[a("strong",[t._v("(More information on the "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm#i34924",target:"_blank",rel:"noopener noreferrer"}},[t._v("date format models"),a("OutboundLink")],1),t._v(" can be found in the Oracle documentation.)")])]),t._v(" "),a("p",[t._v("or:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'January 1, 2000, 00:00 A.M.'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'Month dd, YYYY, HH12:MI A.M.'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'NLS_DATE_LANGUAGE = American'")]),t._v("\n       "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[a("strong",[t._v("(If you are converting language specific terms such as month names then it is good practice to include the 3rd "),a("code",[t._v("nlsparam")]),t._v(" parameter to the "),a("code",[t._v("TO_DATE()")]),t._v(" function and specify the language to be expected.)")])]),t._v(" "),a("h2",{attrs:{id:"generating-dates-with-a-time-component"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#generating-dates-with-a-time-component"}},[t._v("#")]),t._v(" Generating Dates with a Time Component")]),t._v(" "),a("p",[t._v("Convert it from a string literal using "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions183.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_DATE()")]),a("OutboundLink")],1),t._v(":")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2000-01-01 12:00:00'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Or use a "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm#sthref367",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TIMESTAMP")]),t._v(" literal"),a("OutboundLink")],1),t._v(":")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TABLE")]),t._v(" date_table"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  date_value "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INSERT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTO")]),t._v(" date_table "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUES")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2000-01-01 12:00:00'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Oracle will implicitly cast a "),a("code",[t._v("TIMESTAMP")]),t._v(" to a "),a("code",[t._v("DATE")]),t._v(" when storing it in a "),a("code",[t._v("DATE")]),t._v(" column of a table; however you can explicitly "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("CAST()")]),a("OutboundLink")],1),t._v(" the value to a "),a("code",[t._v("DATE")]),t._v(":")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" CAST"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2000-01-01 12:00:00'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("h2",{attrs:{id:"the-format-of-a-date"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#the-format-of-a-date"}},[t._v("#")]),t._v(" The Format of a Date")]),t._v(" "),a("p",[t._v("In Oracle a "),a("code",[t._v("DATE")]),t._v(" data type does not have a format; when Oracle sends a "),a("code",[t._v("DATE")]),t._v(" to the client program (SQL/Plus, SQL/Developer, Toad, Java, Python, etc) it will send 7- or 8- bytes which represent the date.")]),t._v(" "),a("p",[t._v("A "),a("code",[t._v("DATE")]),t._v(" which is not stored in a table (i.e. generated by "),a("code",[t._v("SYSDATE")]),t._v(' and having "type 13" when using the '),a("code",[t._v("DUMP()")]),t._v(" command) has 8-bytes and has the structure (the numbers on the right are the internal representation of "),a("code",[t._v("2012-11-26 16:41:09")]),t._v("):")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("BYTE "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUE")]),t._v("                           EXAMPLE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---- ------------------------------- --------------------------------------")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Year")]),t._v(" modulo "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("256")]),t._v("                 "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("220")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Year")]),t._v(" multiples "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("of")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("256")]),t._v("           "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("7")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("7")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("256")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("220")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2012")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("3")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Month")]),t._v("                           "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("11")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("4")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Day")]),t._v("                             "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("26")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("5")]),t._v("    Hours                           "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("16")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("6")]),t._v("    Minutes                         "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("41")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("7")]),t._v("    Seconds                         "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("9")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("8")]),t._v("    Unused                          "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("0")]),t._v("\n\n")])])]),a("p",[t._v("A "),a("code",[t._v("DATE")]),t._v(' which is stored in a table ("type 12" when using the '),a("code",[t._v("DUMP()")]),t._v(" command) has 7-bytes and has the structure (the numbers on the right are the internal representation of "),a("code",[t._v("2012-11-26 16:41:09")]),t._v("):")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("BYTE "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUE")]),t._v("                           EXAMPLE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---- ------------------------------- --------------------------------------")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Year")]),t._v(" multiples "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("of")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("120")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Year")]),t._v(" modulo "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),t._v("       "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("112")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("120")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("112")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("100")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2012")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("3")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Month")]),t._v("                           "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("11")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("4")]),t._v("    "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Day")]),t._v("                             "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("26")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("5")]),t._v("    Hours "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("                       "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("17")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("6")]),t._v("    Minutes "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("                     "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("42")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("7")]),t._v("    Seconds "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("                     "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("10")]),t._v("\n\n")])])]),a("p",[t._v("If you want the date to have a specific format then you will need to convert it to something that has a format (i.e. a string). The SQL client may implicitly do this or you can explicitly "),a("a",{attrs:{href:"http://stackoverflow.com/documentation/oracle/2087/dates/6849/converting-dates-to-a-string",target:"_blank",rel:"noopener noreferrer"}},[t._v("convert the value to a string"),a("OutboundLink")],1),t._v(" using "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_CHAR( date, format_model, nls_params )")]),a("OutboundLink")],1),t._v(".")]),t._v(" "),a("h2",{attrs:{id:"converting-dates-to-a-string"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#converting-dates-to-a-string"}},[t._v("#")]),t._v(" Converting Dates to a String")]),t._v(" "),a("p",[t._v("Use "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_CHAR( date [, format_model [, nls_params]] )")]),a("OutboundLink")],1),t._v(":")]),t._v(" "),a("p",[a("strong",[t._v("(Note: if a "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements004.htm#i34924",target:"_blank",rel:"noopener noreferrer"}},[t._v("format model"),a("OutboundLink")],1),t._v(" is not provided then the "),a("code",[t._v("NLS_DATE_FORMAT")]),t._v(" session parameter will be used as the "),a("a",{attrs:{href:"http://stackoverflow.com/documentation/oracle/2087/dates/6850/setting-the-default-date-format-model",target:"_blank",rel:"noopener noreferrer"}},[t._v("default format model"),a("OutboundLink")],1),t._v("; this can be different for every session so should not be relied on. It is good practice to always specify the format model.)")])]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("CREATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TABLE")]),t._v(" table_name "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  date_value "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INSERT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTO")]),t._v(" table_name "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUES")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2000-01-01'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INSERT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTO")]),t._v(" table_name "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUES")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-07-21 08:00:00'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INSERT")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INTO")]),t._v(" table_name "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("VALUES")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" SYSDATE "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Then:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_CHAR"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" formatted_date "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" table_name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("FORMATTED_DATE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("--------------")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2000")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("07")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("21")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("07")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("21")]),t._v("\n\n")])])]),a("p",[t._v("And:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_CHAR"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n         date_value"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'FMMonth d yyyy, hh12:mi:ss AM'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'NLS_DATE_LANGUAGE = French'")]),t._v("\n       "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" formatted_date\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   table_name"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("FORMATTED_DATE\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----------------------------")]),t._v("\nJanvier   "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2000")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("12")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v(" AM\nJuillet   "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("21")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("08")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v(" AM\nJuillet   "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("21")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("19")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("08")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("31")]),t._v(" PM\n\n")])])]),a("h2",{attrs:{id:"changing-how-sql-plus-or-sql-developer-display-dates"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#changing-how-sql-plus-or-sql-developer-display-dates"}},[t._v("#")]),t._v(" Changing How SQL/Plus or SQL Developer Display Dates")]),t._v(" "),a("p",[t._v("When SQL/Plus or SQL Developer display dates they will perform an implicit conversion to a string using the default date format model (see the "),a("a",{attrs:{href:"http://stackoverflow.com/documentation/oracle/2087/dates/6850/setting-the-default-date-format-model",target:"_blank",rel:"noopener noreferrer"}},[t._v("Setting the Default Date Format Model"),a("OutboundLink")],1),t._v(" example).")]),t._v(" "),a("p",[t._v("You can change how a date is displayed by changing the "),a("code",[t._v("NLS_DATE_FORMAT")]),t._v(" parameter.")]),t._v(" "),a("h2",{attrs:{id:"extract-the-year-month-day-hour-minute-or-second-components-of-a-date"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#extract-the-year-month-day-hour-minute-or-second-components-of-a-date"}},[t._v("#")]),t._v(" Extract the Year, Month, Day, Hour, Minute or Second Components of a Date")]),t._v(" "),a("p",[t._v("The year, month or day components of a "),a("code",[t._v("DATE")]),t._v(" data type can be found using the "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("EXTRACT( [ YEAR | MONTH | DAY ] FROM datevalue )")]),a("OutboundLink")],1)]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" EXTRACT "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("YEAR")]),t._v("  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-07-25'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("YEAR")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("MONTH")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-07-25'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("MONTH")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-07-25'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("YEAR")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("MONTH")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DAY")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---- ----- ---")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),t._v("     "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("7")]),t._v("  "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("25")]),t._v("\n\n")])])]),a("p",[t._v("The time (hour, minute or second) components can be found by either:")]),t._v(" "),a("ul",[a("li",[t._v("Using "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions016.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("CAST( datevalue AS TIMESTAMP )")]),a("OutboundLink")],1),t._v(" to convert the "),a("code",[t._v("DATE")]),t._v(" to a "),a("code",[t._v("TIMESTAMP")]),t._v(" and then using "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions050.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("EXTRACT( [ HOUR | MINUTE | SECOND ] FROM timestampvalue )")]),a("OutboundLink")],1),t._v("; or")]),t._v(" "),a("li",[t._v("Using "),a("a",{attrs:{href:"http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_CHAR( datevalue, format_model )")]),a("OutboundLink")],1),t._v(" to get the value as a string.")])]),t._v(" "),a("p",[t._v("For example:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("HOUR")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" CAST"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("datetime")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" Hours"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("MINUTE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" CAST"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("datetime")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" Minutes"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n       EXTRACT"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SECOND")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" CAST"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("datetime")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" Seconds\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n  "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-01 09:42:01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("datetime")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL\n"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[t._v("HOURS MINUTES SECONDS\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("----- ------- -------")]),t._v("\n    "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("9")]),t._v("      "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("42")]),t._v("       "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("\n\n")])])]),a("h2",{attrs:{id:"time-zones-and-daylight-savings-time"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#time-zones-and-daylight-savings-time"}},[t._v("#")]),t._v(" Time Zones and Daylight Savings Time")]),t._v(" "),a("p",[t._v("The "),a("code",[t._v("DATE")]),t._v(" data type does not handle time zones or changes in daylight savings time.")]),t._v(" "),a("p",[t._v("Either:")]),t._v(" "),a("ul",[a("li",[t._v("use the "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14225/ch4datetime.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TIMESTAMP WITH TIME ZONE")]),t._v(" data type"),a("OutboundLink")],1),t._v("; or")]),t._v(" "),a("li",[t._v("handle the changes in your application logic.")])]),t._v(" "),a("p",[t._v("A "),a("code",[t._v("DATE")]),t._v(" can be stored as Coordinated Universal Time (UTC) and converted to the current session time zone like this:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" FROM_TZ"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n         CAST"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("\n           TO_DATE"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'2016-01-01 12:00:00'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'YYYY-MM-DD HH24:MI:SS'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n           "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIMESTAMP")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n         "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'UTC'")]),t._v("\n       "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v("\n       AT "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("LOCAL")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("AS")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("time")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v("   DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("If you run "),a("code",[t._v("ALTER SESSION SET TIME_ZONE = '+01:00';")]),t._v(" then the output is:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIME")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("------------------------------------")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("13")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00.000000000")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v("\n\n")])])]),a("p",[t._v("and "),a("code",[t._v("ALTER SESSION SET TIME_ZONE = 'PST';")]),t._v(" then the output is:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("TIME")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("------------------------------------")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("2016")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("01")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("04")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00")]),t._v(":"),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("00.000000000")]),t._v(" PST\n\n")])])]),a("h2",{attrs:{id:"leap-seconds"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#leap-seconds"}},[t._v("#")]),t._v(" Leap Seconds")]),t._v(" "),a("p",[t._v("Oracle "),a("a",{attrs:{href:"http://stackoverflow.com/questions/31136211/how-to-handle-leap-seconds-in-oracle",target:"_blank",rel:"noopener noreferrer"}},[t._v("does not handle leap seconds"),a("OutboundLink")],1),t._v(". See My Oracle Support note "),a("code",[t._v("2019397.2")]),t._v(" and "),a("code",[t._v("730795.1")]),t._v(" for more details.")]),t._v(" "),a("h2",{attrs:{id:"getting-the-day-of-the-week"}},[a("a",{staticClass:"header-anchor",attrs:{href:"#getting-the-day-of-the-week"}},[t._v("#")]),t._v(" Getting the Day of the Week")]),t._v(" "),a("p",[t._v("You can use "),a("a",{attrs:{href:"https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm",target:"_blank",rel:"noopener noreferrer"}},[a("code",[t._v("TO_CHAR( date_value, 'D' )")]),a("OutboundLink")],1),t._v(" to get the day-of-week.")]),t._v(" "),a("p",[t._v("However, this is dependent on the "),a("code",[t._v("NLS_TERRITORY")]),t._v(" session parameter:")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ALTER")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SESSION")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SET")]),t._v(" NLS_TERRITORY "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'AMERICA'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("        "),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-- First day of week is Sunday")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_CHAR"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'1970-01-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'D'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs "),a("code",[t._v("5")])]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ALTER")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SESSION")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SET")]),t._v(" NLS_TERRITORY "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'UNITED KINGDOM'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-- First day of week is Monday")]),t._v("\n"),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TO_CHAR"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("DATE")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'1970-01-01'")]),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'D'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),a("p",[t._v("Outputs "),a("code",[t._v("4")])]),t._v(" "),a("p",[t._v("To do this independent of the "),a("code",[t._v("NLS")]),t._v(" settings, you can truncate the date to midnight of the current day (to remove any fractions of days) and subtract the date truncated to the start of the current iso-week (which always starts on Monday):")]),t._v(" "),a("div",{staticClass:"language-sql extra-class"},[a("pre",{pre:!0,attrs:{class:"language-sql"}},[a("code",[a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" TRUNC"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),t._v(" TRUNC"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v(" date_value"),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token string"}},[t._v("'IW'")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v(" "),a("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" DUAL\n\n")])])])])}),[],!1,null,null,null);s.default=n.exports}}]);