Oracle Date Calculations

Contents

Oracle Date

The DATE datatype returns as number a decimal value representing the number of day.
Oracle can store dates in the Julian era, ranging from January 1, 4712 BCE through December 31, 4712 CE (Common Era, or 'AD'). Unless BCE ('BC' in the format mask) is specifically used, CE date entries are the default.

Decimal date Arithmetic

Since the DATE datatype returns as number a decimal value representing the number of day you can do some simple date arithmetic by adding a decimal number.

 select sysdate + 1  from dual;


 select trunc(sysdate - 90)  from dual;


 select sysdate + 1/24*4  from dual;


Date Conversion functions to_date and to_char


  to_date('Date String', 'Format String')
e.g.
  to_date('20122003 23:12:14', 'DDMMYYYY HH24:MI:SS)


  '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


Oracle uses the functions add_month the calculate proper dates including the end of month problematic:

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


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


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






Status: Published Date: 2017/06/05 14:14:14 Revision: 1.1

Copyright bei Andreas Haack (C) 2014. Diese Seite wird so wie sie ist zur Verfuegung gestellt, ohne irgenweche Garantien der Verwendbarkeit fuer bestimte Zwecke. Die auf dieser Seiten angebrachten Links liegen ausserhalb der redaktionellen Verantwortung von Andreas Haack und es wird keine Haftung oder Garantie uebernommen. Die Seiten sind Copyright (c) 2014 von Andreas Haack. Kein Teil darf ohne die schriftliche Einverstaendnis von Andreas Haack veroeffentlicht werden.
The page is provided 'as is' , without warranty of any kind, express or implied, including but not limited to the warranties of merchantability, fit- ness for a particular purpose and non-infringement. In no event shall Andreas Haack be liable for any claim, damages or other liability. This page is copyrighted property of Andreas Haack. Copyright by Andreas Haack (c) 2014 . No part of this page may be published without written permission for Andreas Haack. A hyper-link may created to this page but NOT to the embedded elements of this page. It may be freely downloaded for private purpose only as long as it is unaltered.