72. Examples - 5

Date Functions

• add_months
• last_day
• months_between
• new_time
• next_day
• round
• sysdate

sysdate And add_months Functions

sysdate:

1. Display the system date

Q. select sysdate from dual;

2. Display 10 days after the system date

Q. select sysdate+10 from dual;

add_months:

1. Display 3 months after from the system date

Q. select add_months(sysdate,3) from dual;

2. Display 12 before from the date '12-mar-1990'

Q. select add_months('12-mar-1990',-12) from dual;

3. Using the fractional number

Q. select add_months('12-mar-1990',1.5) from dual;

  [add_months() converts the fractional number as whole number]

   select add_months('12-mar-1990',1)  from dual;

  [The above two queries are same]

Some Examples:

Q1. select add_months('28-feb-1989',1.9999) from dual;

                        (or)

    select add_months('28-feb-1989',1) from dual;

Q2. select add_months('28-feb-1989',-12.9999) from dual;

                        (or)

    select add_months('28-feb-1989',-12) from dual;

Q3. select add_months('28-feb-1989',0.5) from dual;

                        (or)

    select add_months('28-feb-1989',0) from dual;

last_day Function:

last_day:

1. Display the last day of the system date

Q. select last_day(sysdate) from dual;

2. Display the last day of 31st May 2010

Q. select last_day('31-may-10') from dual;

3. Display last day of the month 3 months after from the system date

Q. select last_day(add_months(sysdate,3)) from dual;

4. select the last day of '16-feb-2010'

Q. select last_day('16-feb-10') from dual;

months_between Function

1. Display months between and system date and '28-jan-2010'

Q. select months_between(sysdate,'28-jan-2010') from dual;

2. Calculate months when two dates fall on the same month.

Q. select months_between('16-feb-2010','11-feb-2010') from dual;

3. Calculate months between '31-mar-1995' and '28-feb-1994'

Q. select months_between('31-mar-1995','28-feb-1994') from dual;

4. Calculate months between '31-mar-1994' and '1-mar-1994'

Q. select months_between('31-mar-1994','1-mar-1994') from dual;

new_time Function

The following example shows the time how it is different from one zone to 

another

Q. select to_char(new_time(to_date('09151994 12:30 AM','MMDDYYYY HH:MI AM'),

   'CST','hdt'),'Month DD,YYYY HH:MI AM') from dual;

next_day Function

1. Display the first monday in the year 2010

Q. select next_day('01-jan-2010','monday') from dual;

2. Display first thrusday in the year 2011

Q. select next_day('01-jan-2011','thursday') from dual;

3. Display next friday of the system date

Q. select next_day(sysdate,'friday') from dual;

to_date and to_char with round function

round():

1. Write a query to round back to current century

Q. select to_char(round(to_date('22-may-2010'),'cc'),'DD-mon-yyyy') from 

   dual;

2. Write a query to round up to next century

Q. select to_char(round(to_date('22-sep-2094'),'cc'),'DD-mon-yyyy') from 

   dual;

3. Write a query to round down to the first of the year

Q. select round(to_date('22-may-2010'),'yyyy') from dual;

4. Write a query to round up to the first of the year

Q. select round(to_date('22-sep-2010'),'year') from dual;

5. Write a query to round up to the quarter(first date in the quarter)

Q. select round(to_date('12-mar-1994'),'Q') from dual;

6. Write a query to round to the quarter

Q. select round(to_date('12-aug-2010'),'Q') from dual;

   select round(to_date('12-dec-2010'),'Q') from dual;

7. Write a query to print the day of the particular date

Q. select to_char(to_date('22-may-2010'),'day') from dual;

   select to_char(to_date(sysdate,'day') from dual;

8. Write a query round to date of nearest sunday for '22-may-2010'

Q. select round(to_date('22-may-2010'),'ww') from dual;

9. write a query round back to nearest day(time always midnight).

Q. select to_char(round(to_date('11-sep-2010 10:00 AM','DD-Mon-yy HH:MI AM'),

   'DD'),'DD-Mon-yy HH:MI AM') from dual;

10. write a query to round forward to the nearest day

Q.  select to_char(round(to_date('11-sep-2010 4:00 PM','DD-Mon-yy HH:MI AM'),

    'DD'),'DD-Mon-yy HH:MI AM') from dual;

11. Write a query to round back to the nearest hour

Q.   select to_char(round(to_date('11-sep-2010 4:17PM','DD-Mon-yy HH:MI AM'),

     'HH'),'DD-Mon-yy HH:MI AM') from dual;

sysdate And add_months Functions

sysdate:

1. Display the system date

Q. select sysdate from dual;

2. Display 10 days after the system date

Q. select sysdate+10 from dual;

add_months:

1. Display 3 months after from the system date

Q. select add_months(sysdate,3) from dual;

2. Display 12 before from the date '12-mar-1990'

Q. select add_months('12-mar-1990',-12) from dual;

3. Using the fractional number

Q. select add_months('12-mar-1990',1.5) from dual;

  [add_months() converts the fractional number as whole number]

   select add_months('12-mar-1990',1)  from dual;

  [The above two queries are same]

Some Examples:

Q1. select add_months('28-feb-1989',1.9999) from dual;

                        (or)

    select add_months('28-feb-1989',1) from dual;

Q2. select add_months('28-feb-1989',-12.9999) from dual;

                        (or)

    select add_months('28-feb-1989',-12) from dual;

Q3. select add_months('28-feb-1989',0.5) from dual;

                        (or)

    select add_months('28-feb-1989',0) from dual;