Oracle Date Calculations

Oracle Date

Decimal date Arithmetic

• add 24 hours shows tomorrow

 select sysdate + 1  from dual;

• 90 days ago (midnight/date part)

 select trunc(sysdate - 90)  from dual;

• In 4 hours

 select sysdate + 1/24*4  from dual;

• Delta between two dates ===

  select to_date ('13.04.2013') - to_date ('13.01.2013') from dual;
  90 days

Date Conversion functions to_date and to_char

• to_date convert string to a oracle date

  to_date('Date String', 'Format String')

  to_date('20122003 23:12:14', 'DDMMYYYY HH24:MI:SS)

• to_char convert date to a string

  'StartDate:' || to_char(start_date, 'DDMMYYYY HH24:MI:SS)

Format Strings

 YEAR 		Year, spelled out 
 YYYY 		4-digit year 
 YYY
 YY
 Y 		Last 3, 2, or 1 digit(s) of year. 
 IYY
 IY
 I 		Last 3, 2, or 1 digit(s) of ISO year. 
 IYYY 		4-digit year based on the ISO standard 
 RRRR 		Accepts a 2-digit year and returns a 4-digit year.
 A 		value between 0-49 will return a 20xx year.
 A 		value between 50-99 will return a 19xx year. 
 Q 		Quarter of year (1, 2, 3, 4; JAN-MAR = 1). 
 MM 		Month (01-12; JAN = 01). 
 MON 		Abbreviated name of month. 
 MONTH 		Name of month, padded with blanks to length of 9 characters. 
 RM 		Roman numeral month (I-XII; JAN = I). 
 WW 		Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. 
 W 		Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. 
 IW 		Week of year (1-52 or 1-53) based on the ISO standard. 
 D 		Day of week (1-7). 
 DAY 		Name of day. 
 DD 		Day of month (1-31). 
 DDD 		Day of year (1-366). 
 DY 		Abbreviated name of day. 
 J 		Julian day; the number of days since January 1, 4712 BC. 
 HH 		Hour of day (1-12). 
 HH12 		Hour of day (1-12). 
 HH24 		Hour of day (0-23). 
 MI 		Minute (0-59). 
 SS 		Second (0-59). 
 SSSSS 		Seconds past midnight (0-86399). 
 FF 		Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'. 
 AM, 		A.M., PM, or P.M. Meridian indicator 
 AD 		or A.D AD indicator 
 BC 		or B.C. BC indicator 
 TZD 		Daylight savings information. For example, 'PST' 
 TZH 		Time zone hour. 
 TZM 		Time zone minute. 
 TZR 		Time zone region. 

Month Calculations

• 29 January 2013 plus one month is 28 February

   SELECT add_months(TO_DATE('29-JAN-2013'), 1) FROM DUAL;
   28.02.2013

• 29 January 2016 (leap year) plus one month is 29 February

   SELECT add_months(TO_DATE('29-JAN-2016'), 1) FROM DUAL;
   29.02.2016

• 29 January 2013 plus 36 month (3 years) is 29 February

   SELECT add_months(TO_DATE('28-FEB-2013'), 36) FROM DUAL;
   29.02.2016

• List of February + one month is last of March

 SELECT add_months(TO_DATE('28-FEB-2013'), 1) FROM DUAL;
 31.03.2016