SQL Functions Part3 (Date Functions)

We have many date functions available in SQL which are very useful while working with data of date type in SQL. Date functions will accept date data as input and will produce either date or numerical output depending on the function.

We have many date functions available in SQL. We can do arithmetic operations also on dates. So, First I will discuss about the arithmetic operations which we can do on date data. Before discussing about this we need to know about SYSDATE function.

SYSDATE function :


   SYSDATE is a function which will always give the current date according to the database system. So,if we want to use current date in any of the SQL queries then we can use SYSDATE function. This is the only function which will not contain any arguments.

 SELECT sysdate FROM dual;

This query will give the current date according to database system.

Arithmetic Operations On Date Data ::


   We can add a number to date by using + operator. If we add any number to date, then database will provide the result by adding those many number of days to that date.

Ex : SELECT sysdate +  4 From dual;

 This query will give the result by adding 4 days to sysdate . For ex , if it is 13-nov-2011 then we will get result as 17-nov-2011.

    We can subtract a number from a date by using - operator. If we subtract any number from date, then database will provide the result by subtracting those many number of days from that date.

Ex : SELECT sysdate -  4 From dual;

 This query will give the result by subtracting 4 days from sysdate . For ex , if it is 13-nov-2011 then we will get result as 09-nov-2011.

   We can subtract two dates also using - operator. This will give us the difference between those two dates in terms of number of days. So, if we want to find date difference in terms of number of days then we have to use - operator.

 Ex : SELECT to_date(17-jun-2011)-to_date(14-jun-2011) from dual;

  This will give us the result  as 3. Here I have used to_date function because, generally arithmetic operators will expect numeric values. So, even if we provide date values in valid format it will treat it as number and will throw invalid number exception. To avoid it, we have to convert these two dates using to_date function. So, that it will consider these two values as dates. I will discuss about this to_date function in detail in my coming posts.

So, this is about the arithmetic operations which we can do on date data.

Date Functions ::

  We have many date functions available in SQL. I will explain about some important date functions in this post.

ROUND() Function :


   Round function is used to rounding off  the date values. We can round the date values depending on either month or year . For this function input is date and output is also a date.

Syntax : Round ( date , 'MONTH' or 'YEAR')

  In this syntax,

   ---->  we have to give second argument as either 'MONTH' or 'YEAR' depending on with respect to
             what we want to round the date.

    The logic for rounding of with month is,

   * if date in input date is <15 then we will get first of current month in input as output.
   * if date in input date is >=15 then we will get first of next month  as output.

  Ex : SELECT ROUND(to_date('14-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011' as day<15.

    SELECT ROUND(to_date('16-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-aug-2011' as day>=15.

    The logic for rounding of with year is,

   * if month in input date is <6 then we will get first of current year in input as output.
   * if month in input date is >=6 then we will get first of next year  as output.

   Ex : SELECT ROUND(to_date('14-mar-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011' as month<6.

    SELECT ROUND(to_date('16-jul-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2012' as month>=6.

TRUNC() Function :


   Trunc function is used to truncate  the date values. We can truncate the date values depending on either month or year . For this function input is date and output is also a date.

Syntax : Trunc ( date , 'MONTH' or 'YEAR')

  In this syntax,

   ---->  we have to give second argument as either 'MONTH' or 'YEAR' depending on with respect to
             what we want to truncate the date.

    The logic for truncating of with month is,

   * irrespective of date in input date, we will get first of current month in input as output.

  Ex : SELECT TRUNC(to_date('14-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011'.

    SELECT TRUNC(to_date('16-jul-2011'),'MONTH') FROM dual;

   For this query we will get output as , '01-jul-2011' .

    The logic for rounding of with year is,

   *  irrespective of month in input date, we will get first of current year in input as output.

   Ex : SELECT TRUNC(to_date('14-mar-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011'.

    SELECT TRUNC(to_date('16-jul-2011'),'YEAR') FROM dual;

   For this query we will get output as , '01-jan-2011'.     


  MONTHS_BETWEEN() Function :


      Months_Between() function is used to find difference between two dates in terms of number of months. For this function input is dates and the output is number.

 Syntax : MONTHS_BETWEEN ( date1, date2 )

 Ex : SELECT MONTHS_BETWEEN ( to_date('14-jul-2011'), to_date('21-sep-2011')) FROM dual;

  For this query we will get output as -2.2258065. We got negative value because we gave least value as the  first  input.

  ADD_MONTHS() Function :

      Add_Months() function is used to add certain number of months to the input date.  For this function input is date and output is also a date.

 Syntax : ADD_MONTHS(date, number_of_days)

  In this syntax ,

   number_of_days is the number which will decide number of months to be added to input date.

 Ex : SELECT ADD_MONTHS (to_date('12-jun-2011'), 4) FROM dual;

 This will give output as '12-oct-2011' by adding 4 months to input date.

  NEXT_DAY() Function :


    Next_day() function is used to find the next day as specified in the syntax. For this function input is a date and output is also a date.

  Syntax : NEXT_DAY(date,'DAY')

  In this syntax,

     --> 'DAY' refers to the day which we want after the specified date. List of values for this day are,

           'MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT, 'SUN'.

      This means that, if we give day as 'THU' in the syntax, then the database will provide the output as the date on which next Thursday will come from the input date.

  Ex : SELECT NEXT_DAY(sysdate, 'FRI') FROM dual;

  Let us say here sysdate is '13-nov-2011' which is Sunday. So, we will get the output for the above query as '18-nov-2011' . This is the date on which we will get immediate next friday after the date '13-nov-2011'.


  LAST_DAY() Function :


     Last_Day() function is used to find the date of last day of the month specified in the input date. For this function input is a date and output is also a date.

   Syntax :  LAST_DAY (date);

  Ex : SELECT LAST_DAY (to_date('15-jul-2011')) From dual;

  This will give us the output as '31-jul-2011' as it is the last day of july month which is specified in the input
  date.

 These are the some of the important date functions which are available in SQL.