Oracle Date Time Functions

SYSDATE

Sysdate represents current date and time in Oracle. 

Example;

select sysdate from dual

Result;

29/06/2009 1:41:10 PM

 

ADD_MONTHS

This function adds specified amount of months to date given. Its usage and results are like this;

declare

  x date;

begin

           x  :=  ’25/10/2009′;

           x := add_months(x,1);

           dbms_output.putline(x);

end;

Result;

25/11/2009

 

MONTHS_BETWEEN

This function is used to get the months between two date values given. The only detail with this function is that this function returns a float value. Because of this reason you can either use this value directly, truncate it, floor it, ceil it or round it. It is up to your usage.

Example;

select months_between(‘25/10/2009′,’26/12/2009′) from dual

Result;

-1.03225806451613

This value tells us that first date is past according to second date and more than one months are between these dates.

 

Date Format

To change the date format of our session we can use this Oracle DB command;

ALTER SESSION SET NLS_DATE_FORMAT = ‘DD/mm/YYYY HH24:MI:SS’;

This code changes our date time format to example given;

Result

25/10/2009 15:12:42

Date Formats Ex

There are so many date formats at Oracle that can be used. If you ask why we need this formats it is clear for me to say that you will absolutely need this formats in order to show your values correctly to en user.

You could want to show current date like this; ‘25/10/2009′ or like this ‘25-10-2009′ or just with numbers ‘25102009′. Every companies has their own styles of coding. Date formats are one of them. One company could tell you that they want you to show every dates at dd/mm/yyyy format.  If you do not format your dates each time you parse it to this format it can work fine at first. But, whenever someone tries to change machine’s NLS settings to another values then you are having problem. To avoid this kind of situations you should be using date formats for following situations;

        – When you are parsing date value to a string value (varchar…);

                 * You can use to_char(date,’format’)–date and format are in varchar format.

                 example;

                 select to_char(sysdate,’dd/mm,yyyy’) from dual;

                 Retuns

                  29/06,2009

                 Example 2

                 select to_char(sysdate,’day-mon-year’) from dual

                 Retuns

                 mon-june-two thousand nine

                

                   You can convert your date values at any format you want. As in the second example you can convert date values to human language format.  It takes this formats from NLS(Language) settings.

 

           – When you want to convert char to date;

                    * You can use to_date function

                     Example

                     declare

                           x date;

                     begin

                             x := to_date(‘25/10/2009′,’dd/mm/yyy’) ;

                     end;

                     Warning: Although Oracle recognises your computer’s date format automatically to keep your software portable you should specify format parameter each time you use it.

NEXT_DAY

This function returns the first date that the day of week specified comes.

Example;

SELECT NEXT_DAY(‘27/06/2009′, ‘MON’) FROM dual;

Returns

29/06/2009

 

ROUND

 
This function round the date value given. It uses the standard rounding rules. Rounds to nearest date.

Example

SELECT ROUND(TO_DATE(‘27-11-01′),‘mm’) NEW_YEAR from dual

 

Returns

01/12/0001