Add a day/hour/minute/second to a date value in oracle SQL

                                  DATE is the datatype that we are all familiar with when we think about representing date and time values. It has the ability to store the month, day, year, century, hours, minutes, and seconds. It is typically good for representing data for when something has happened or should happen in the future. The problem with the DATE datatype is its' granularity when trying to determine a time interval between two events when the events happen within a second of each other. This issue is solved with the TIMESTAMP datatype.

In order to represent the date stored in a more readable format, the TO_CHAR function has traditionally been wrapped around the date


Examples:-

select sysdate from dual          

select sysdate-2 from dual  -- 2 days back

select SYSDATE+1/24 from dual  --- Next one hour

select SYSDATE-1/24 from dual  -- before one hour   

 select sysdate, sysdate+1/24, sysdate +1/1440,sysdate +1/86400 from dual;


select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)

Dates in Hours/Minutes/Secs

--> Extract date/hours/minutes/seconds/mseconds from a date
--------------------------------------------------------------

select to_char(day, 'dd-mon-yyyy hh24:mi:ss' )                 Input_Date,
         trunc(day)                                                             Day,
         trunc(time*24)                                                        Hours,
         trunc(time*24*60 - 60*trunc(time*24))                       Minutes,
         trunc(time*24*60*60 - 60*trunc(time*24*60))              Seconds,
         trunc(time*24*60*60*100 - 100*trunc(time*24*60*60)) mseconds
  from (select &day                      day,
                    &day - trunc(&day)  time
             from dual
          ) 

--> Get difference of two dates in days/hours/minutes/seconds/mseconds
-------------------------------------------------------------------------

select to_char( date1, 'dd-mon-yyyy hh24:mi:ss' )     Date1,
          to_char( date2, 'dd-mon-yyyy hh24:mi:ss' )     Date2,
          trunc( date2-date1 )                                      Days,
          trunc( mod( (date2-date1)*24, 24 ) )                Hours,
          trunc( mod( (date2-date1)*24*60, 60 ) )           Minutes,
          trunc( mod( (date2-date1)*24*60*60, 60 ) )      Seconds,
          trunc( mod( (date2-date1)*24*60*60*60, 60 ) ) mSeconds
  from (select date_column1 date1
                    ,date_column2 date2
            from &table_name
          )

--> Adding specific number of Days/Hours/Minutes/Secs to a Date
--------------------------------------------------------------------
 select sysdate inputdate
         , sysdate + (:day) + (:Hours/24) + (:Minutes/(24*60)) + (:Secs/(24*60*60)) newdate
   from dual;

function DateDiff

create or replace function DateDiff(DatePart varchar, StartDate date, EndDate date)
return number
as
result    number;
sType    char(1);
begin
    sType := substr(upper(DatePart), 1, 1);
    if sType = 'S' then
        result := trunc(86400 * (EndDate - StartDate));
    end if;
    if sType = 'H' then
        result := trunc(((86400 * (EndDate - StartDate)) / 60) / 60);
    end if;
    if sType = 'D' then
        result := trunc((((86400 * (EndDate - StartDate)) / 60) / 60) / 24);
    end if;
    if sType = 'W' then
        result := trunc(((((86400 * (EndDate - StartDate)) / 60) / 60) / 24) / 7);
    end if;
    if sType = 'Y' then
        result := trunc(months_between(EndDate, StartDate) / 12);
    end if;
    if sType = 'N' then
        result := trunc((86400 * (EndDate - StartDate)) / 60);
    end if;
    if sType = 'M' then
        if upper(DatePart) like 'MI%' then
            result := trunc((86400 * (EndDate - StartDate)) / 60);
        else
            result := trunc(months_between(EndDate, StartDate));
        end if;
    end if;
return result;
exception when others then return null;
end;

Function DateADD

Create or replace  Function DateADD(vchDatePart  varchar , intOP   in   int,
dt date)
return date
as
    dd      int;
    mm      int;
    yyyy    int;
    hh      int;
    NN      int;
    SS      int;
    v       date;
    lintOP  int;
Begin
    lintOP := intOP;
--GEO--C--  INcreament  Days
    if upper(vchDatePart) like 'D%'  then
        return dt + intOP;
    end if;
    dd  := to_number(to_Char(dt,'dd'));
    mm  := to_number(to_Char(dt,'MM'));
    yyyy:= to_number(to_Char(dt,'yyyy'));
    HH  := to_number(to_Char(dt, 'HH'));
    NN  := to_number(to_Char(dt, 'MI'));
    SS  := to_number(to_Char(dt, 'SS'));
--GEO--C--  INcreament  Year
    if upper(vchDatePart) like 'Y%' then
        yyyy:= yyyy+ lintOP;
    end if;
--GEO--C--  INcreament  Month.
    if upper(vchDatePart) like 'M%' then
        yyyy:= yyyy+round(lintOP/12);
        mm  := mm+mod(lintOP,12);
    end if;-->MM
    if upper(vchDatePart) like 'H%' then
        dd  :=  dd  +   round(lintOP/24);
        hh  :=  hh  +   mod(lintOP,24);
    end if;--> hh
    if upper(vchDatePart) like 'N%' then
        dd  :=  dd  +   round(lintOP/(24*60));
        hh  :=  hh  +   round(lintOP/60);
        NN  :=  NN  +   mod(lintOP , 60);
    end if;--> MInutes
    if upper(vchDatePart) like 'S%' then
        dd  :=  dd  +   round(lintOP/(24*60*60));
        hh  :=  hh  +   round(lintOP/60*60);
        NN  :=  NN  +   round(lintOP/60);
        NN  :=  NN  +   MOD(lintOP,60);
    end if;--> SS
        v   := LAST_DAY(to_date('01/'||to_char(mm,'09')||'/'|| to_char(yyyy,
'0009'),'dd/mm/yyyy'));
        if dd > to_number(to_Char(v,'DD'))  then
            dd := to_number(to_Char(v,'DD'));
        end if;
    return to_date(lpad(dd,2,'0')||to_char(mm,'09')||'/'|| to_char(yyyy,
'0009')||' '||lpad(hh,2,'0')||':'||lpad(NN,2,'0')||':'||lpad(SS,2,'0'),
'dd/mm/yyyy HH24:MI:SS') ;
    exception  when others then return null ;
End;

FIRST AND LASTDAY OF MONTH

SELECT LAST_DAY(:datecol1) FROM  DUAL;


select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual


select trunc(trunc(sysdate,'MM')-1,'MM') "First Day of Last Month" FROM DUAL

Date format

ICX: Date format mask
ICX: Date language
ICX: Language
ICX: NLS Sort
ICX: Numeric characters
ICX: Territory

ICX_DATE_FORMAT_MASK

Self Service Personal Home Page Mode / APPLICATIONS_HOME_PAGE

Determines the look-and-feel of the Oracle Self-Service Applications Personal Home Page.
Valid values include:
-Framework only - This is the new OA Framework personal home page.
-Personal Home Page - This the earlier blue/gray personal home page.
-Personal Home Page with Framework - This is combination: users login to the old-style personal home page, but when they select a responsibility, the new OA Framework navigation page displays instead of the old blue/gray menu.

Application Framework Agent / APPS_FRAMEWORK_AGENT

Specifies the Java listener for your HTTP server (the host and port for the web server that will be used by OA Framework applications). It can be set at the Site and the User level.
Value (provide your own hostname and portname):
"http://hostname:portname"
Warning: Both the HTTP server and the Java listener should be properly configured and started before the OA Framework applications can be launched.

Apps Servlet Agent / APPS_SERVLET_AGENT

Determines the location where servlets are executed.
Value (provide your own hostname and portname):
"http://hostname:portname/oa_servlets"
Note this is a preexisting Oracle Applications profile option that must be set for graphs.

ICX: Limit Time / ICX_LIMIT_TIME

Determines the maximum servlet session length in hours.

ICX:Session Timeout / ICX_SESSION_TIMEOUT

Maximum idle time for the Oracle Applications user session (specified in minutes).
Tip: If passivation is enabled, this value should be longer than the ICX: Limit Time value to allow users to resume suspended transactions without being redirected to the login page (this happens only when the Oracle Applications user session times out, not the servlet session).

Upload File Size Limit / UPLOAD_FILE_SIZE_LIMIT

Specifies the maximum allowable file size in KB for uploaded attachments.
For example, if the limit should be 2MB, this value should be set to 2000 or 2000K.

ICX: MatchCase View / ICX_MATCHCASE_VIEW

This profile option controls the "Match Case" checkbox in the "Advanced Search" region of Web Inquiries. Setting this profile option value to "Checked" or "Hidden" helps avoid running poor performing queries which would normally disable indexes using an upper() clause. This profile option can be set at all levels. Valid values include:
• Unchecked - the Match Case checkbox will be rendered in an unchecked state
• Checked - the Match Case checkbox will be rendered in a checked state
• Hidden - the Match Case checkbox will NOT be rendered, and it will behave as if checked. Instead of the checkbox, the UI displays a message that says "Match Case has been selected for you."

Server Timezone / SERVER_TIMEZONE_ID

The time zone for the database server. It is assumed that all dates stored in the database will be interpreted relative to this time zone. This profile is only updatable at the Site level.

Client Timezone / CLIENT_TIMEZONE_ID

The time zone for the client (user). This profile is updatable at all levels - Site, Application, Responsibility and User. Fields that specify a date and time will be queried and displayed to the user after automatically applying a time zone conversion as indicated by the Server Timezone and Client Timezone profiles. Conversely, a date and time entered by the user will undergo the opposite conversion before being stored into the database.

ICX: Date format mask / ICX_DATE_FORMAT_MASK

The format used when displaying date fields. When a field displays both date and time, the date component will be displayed in the format specified here, and the time component will be displayed in a 24 hour format including hours, minutes and seconds

ICX: Language / ICX_LANGUAGE

User session language.

ICX: Territory / ICX_TERRITORY

User session territory or country.

Date format query

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. Use fractions to add hours, minutes or seconds to the date. Look at these examples:

SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW                  NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Here are a couple of examples:

Description	Date Expression
Now	SYSDATE
Tomorow/ next day	SYSDATE + 1
Seven days from now	SYSDATE + 7
One hour from now	SYSDATE + 1/24
Three hours from now	SYSDATE + 3/24
An half hour from now	SYSDATE + 1/48
10 minutes from now	SYSDATE + 10/1440
30 seconds from now	SYSDATE + 30/86400
Tomorrow at 12 midnight	TRUNC(SYSDATE + 1)
Tomorrow at 8 AM	TRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnight	TRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.m	TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24)