09 June, 2012

Dates in Oracle Database


Introduction
Oracle database stores dates in an internal numeric format, representing the century, year, month, day, hours, minutes, and seconds. The default display and input format for any date is DD-MON-YY(You can change it). Valid Oracle dates are between January 1, 4712 B.C. and December 31, 9999  .

Oracle database supports simple date(True Date) and time(Date and Time) which stores in standard internal format.
Oracle supports a set of built ins function for manipulating date and time.

Date Format
As we mentioned before that oracle stores Dates internal in numeric format, but in displaying dates it displays it in different formats.
The default date format is "DD-MON-YY", the conversion of formats is done by TO_CHAR function that has below syntax

 TO_CHAR(DATE,'DATE_FORMAT','NLS_PARAMETERS');  

Date : is date value
NLS_PARAMETERS : is an optional and determine different NLS parameters in conversion.
DATE_FORMAT: It contains different format from below table


Parameter Explanation
YEAR Year, spelled out alphabetically
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 the month.
MONTH The name of month, padded with blanks to length of 9 characters.
RM Roman numeral month (I-XII; JAN = I).
WW The week of the year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W The week of the month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW The week of year (1-52 or 1-53) based on the ISO standard.
D Day of the week (1-7). Sunday is day 1 when nls_territory is set to 'AMERICA' but differs if another nls_territory is set (i.e. 'UNITED KINGDOM' or 'GERMANY' - in these cases Monday is 1.
DAY Name of the day.
DD The day of month (1-31).
DDD The day of year (1-366).
DY Abbreviated name of the day. (Mon, Tue, Wed, etc)
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 Number of 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, 'FF5'.
AM, A.M., PM, or P.M. Meridian indicator
AD or A.D AD indicator
BC or B.C. BC indicator
TZD Daylight savings identifier. For example, 'PST'
TZH Time zone hour.
TZM Time zone minute.
TZR Time zone region.

Note that any text rather than date formats used in Date formats will be printed as text like - , / , "of", "Any Text between double quotation" ,  ..... etc.
Examples
 SELECT TO_CHAR (SYSDATE, 'DD/MM/YYYY') FROM DUAL;  
 --15/06/2012  
   
 SELECT TO_CHAR (SYSDATE, 'Month DD, YEAR') FROM DUAL;  
  -- June    17, TWENTY TWELVE  
   
 SELECT TO_CHAR (SYSDATE, 'FMMonth DD, FMYEAR') FROM DUAL;  
 -- June 17, TWENTY TWELVE  
   
 SELECT TO_CHAR (sysdate, ' DDth "of" MON') FROM DUAL;  
 -- 15TH of JUNE  
   
 SELECT TO_CHAR (sysdate, 'FMDay, DDth Month YYYY', 'NLS_DATE_LANGUAGE=Dutch')  
  FROM DUAL;  
 -- Vrijdag, 15TH Juni 2012  
   
 SELECT TO_CHAR (SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;  
 -- 2012-06-15 11:14:59  
   
 SELECT TO_CHAR (SYSDATE, 'FMDdspth "of" Month YYYY FMHH:MI:SS AM') FROM DUAL;  
 --Fiveteenth of June 2012 11:16:17 AM  


Get Current Date and Time.
We always need to get current date and time in our code, Oracle produce SYSDATE, CURRENT_DATE, LOCALTIMESTAMP, SYSTIMESTAMP, CURRENT_TIMESTAMP functions for this purpose.
  --Get True Date from oracle database server  
  SELECT SYSDATE FROM DUAL;   
  -- 15-Jun-12 10:20:00 AM   
   
    
  --Get True date from oracle session timezone  
  SELECT CURRENT_DATE FROM DUAL;   
  --15-Jun-12 10:20:00 AM   
   
   
 --Get Timestamp with timezone from oracle database server    
  SELECT SYSTIMESTAMP FROM DUAL;   
  --15-Jun-12 10:20:00.974253 AM +03:00  
    
    
 --Get Timestamp from oracle session timezone    
  SELECT LOCALTIMESTAMP FROM DUAL;   
  --15-Jun-12 10:20:00.974253 AM   
   
   
 --Get Timestamp with timezone from oracle session timezone    
  SELECT CURRENT_TIMESTAMP FROM DUAL;  
  --15-Jun-12 10:20:00.974253 AM +03:00  

Date Conversions
We use TO_DATE, TO_TIMESTAMP to convert string values to date or timestamp, their syntax is same as TO_CHAR that was mentioned before.

Examples
 SELECT TO_DATE ('2012/06/18', 'yyyy/mm/dd') FROM DUAL;  
   
 SELECT TO_TIMESTAMP ('08-Jun-12 05:34:34.123456', 'DD-Mon-RR HH24:MI:SS.FF')  
  FROM DUAL;  

Date Built-ins Functions
Oracle produce a set of built-ins functions for handling different operation in Date data type

1- ADD_MONTHS(date DATE , n INTEGER) 
It adds ( n ) number of months to date

2- GREATEST( d1 DATE, d2 DATE, d3 DATE , ....... )
It return the latest date of d1, d2, d3, .......

3- LEAST( d1 DATE, d2 DATE, d3 DATE , ....... )
It return the earliest date of d1, d2, d3, .......

4- LAST_DAY( d1 DATE)
It return date of last day of month in d1 date.

5- MONTHS_BETWEEN( d1 DATE, d2 DATE)
It return the number of months separating two days.
Note d1 must be the later date and d2 is the earlier date.

6- NEXT_DAY( date DATE, day VARCHAR2)
It return the date of next day appropriate to day
day are any value from (SUN, MON, TUE, WED, THU, FRI, and SAT)
Example : SELECT NEXT_DAY (SYSDATE, 'FRI') FROM DUAL;

7- TRUNC(d Date, fmt Varchar2)
It trunc date to specific format

Example : SELECT TRUNC(SYSDATE,'MM') FROM DUAL;

8- ROUND( d Date, fmt Varchar2)
It rounds date to specific format

Example : SELECT ROUND(SYSDATE,'MM') FROM DUAL;

9- EXTRACT ( YEAR / MONTH / WEEK / DAY / HOUR / MINUTE / TIMEZONE  FROM DATE)
It extract specific portion of date and time from date
The portion that you can extract from date is ( year, month, week, day, hour, minute, timezone).

Example :
SELECT EXTRACT (MINUTE FROM SYSTIMESTAMP) FROM DUAL;
SELECT EXTRACT (YEAR FROM SYSDATE) FROM DUAL;

You can also use other functions like FLOOR, CEIL, DUMP, MIN, MAX.

Date and Time Calculations
1- Date and time can be used in arithmetic operations( addition and subtract) only.

2- You can compare dates in logical conditions using ( = , <> , != , > , >= , < , <= , ... etc ).

3- You can add and subtract number from date and that will yield date also
     Example : SELECT SYSDATE + 5 FROM DUAL;

4- You can subtract two dates and the result will be the number of days between two days.
     Example : SELECT SYSDATE - SYSDATE FROM DUAL;
     Note : you can not add two dates.

Thanks
Mahmoud A. El-Sayed

5 comments:

  1. Quite a nice coverage Mahmoud, Dates are important thing to understand in every database. specially difference between two dates, which is also a popular SQL questions like truncate vs delete.

    Thanks
    Javin

    ReplyDelete
  2. Oracle Understand the Default date format by default other wise we have to use the conversion function to convert the date into other format.

    We can change the default date format for the session .

    Alter session set NLS_DATEFORMAT='MM:DD:YYYY HH:MI:SS';

    Implict conversion are also take place like

    date = '22-nov-1996' ... its a string but oracle implictly convert this acc to nls_date_format

    'nov-22-96' this can not be converted by Implict for this we have to use the conversion functions.

    There are FM parameters with to char function as well to surpass the leading zero and blanks ... But its a very good topic but there can be more details on this ..
    any how Good work Mohmmad ..

    ReplyDelete
    Replies
    1. Thanks Arun for your reply and mention good points.

      I already mention NLS_PARAMETERS in TO_CHAR function
      TO_CHAR(DATE,'DATE_FORMAT','NLS_PARAMETERS');

      but I didn't mention how to change NLS_PARAMETERS in session and system scope.

      I did example use FM parameters
      SELECT TO_CHAR (sysdate, 'FMDay, DDth Month YYYY', 'NLS_DATE_LANGUAGE=Dutch')
      FROM DUAL;

      Thanks for your good comment

      Delete
  3. This comment has been removed by the author.

    ReplyDelete

ADF : Scope Variables

Oracle ADF uses many variables and each variable has a scope. There are five scopes in ADF (Application, Request, Session, View and PageFl...