How to find first and last date of a month in SQL

How to find first and last date of a month in SQL

Working with dates is fun. In this post we are going to show a method to easily find the first and last date of the current month, previous month, and next month.

Knowledge about this will come in handy if you i.e. want to do a year-over-year sales analysis, or maybe you would like to take the current month’s sales figures and compare them to what is budgeted for next month’s sale.

Depending on how your data is organized or partitioned, there are time you want to use the last date of a month in i.e. a BETWEEN statement in SQL.

Here is a query that selects the first and last date of  a month, for last month, current month, and next month.

SELECT TRUNC (SYSDATE) today                                     -- Last Month,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, -1), 'YYYYMM'), 'YYYYMM')

          first_date_last_month,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM') - 1

          last_date_last_month                                                   -- Current Month

                              ,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, 0), 'YYYYMM'), 'YYYYMM')

          first_date_this_month,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM') - 1

          last_date_this_month                                                     -- Next Month

                              ,

       TO_DATE (TO_CHAR (ADD_MONTHS (SYSDATE, +1), 'YYYYMM'), 'YYYYMM')

          first_date_next_month,

       TO_DATE (TO_CHAR (ADD_MONTHS (:P_CURR_BEGIN_DATE, +2), 'YYYYMM'), 'YYYYMM') - 1

          last_date_next_month

  FROM DUAL;

Oracle Procedure to send emails to single or multiple receipients

Oracle Procedure to send emails to single or multiple recipients:

1. The below code is used to send email to single recipient using oracle Store Procedure:

 /********************************************************************************

           Function Name: sendmail_from_plsql
              Description: This function sends and Email through the system mail host
                           to the address in the "p_rcptaddr" input parameter.
             Input Params: p_sendaddr : Email address of the sender.
                           p_rcptaddr : Email address of the receipient.
                           p_ccaddr : Email address of the cc address.
                           p_subject: Subject title of the Email message.
                           p_body   : Body of the mail message.
           Output Params:  return boolean value, TRUE if sendmail successful else FALSE
               p_error_msg  - Error msg out, If there is error in sending mail
    ********************************************************************************/

FUNCTION sendmail_from_plsql (p_sendaddr    IN     VARCHAR2,
                                 p_rcptaddr    IN     VARCHAR2,
                                 p_ccaddr      IN     VARCHAR2,
                                 p_subject     IN     VARCHAR2,
                                 p_body        IN     VARCHAR2,
                                 p_error_msg      OUT VARCHAR2)
      RETURN BOOLEAN
   IS
   BEGIN
      mailhost := 'xxxxxxxxxx';-- give your mail host name over here
      mail_conn := UTL_SMTP.open_connection (mailhost, 25);
      UTL_SMTP.helo (mail_conn, mailhost);
      UTL_SMTP.mail (mail_conn, p_sendaddr);
      UTL_SMTP.rcpt (mail_conn, p_rcptaddr);

      IF p_ccaddr IS NOT NULL
      THEN
         UTL_SMTP.rcpt (mail_conn, p_ccaddr);
      END IF;

      v_mesg :=
            'Date: '
         || TO_CHAR (SYSDATE, 'dd Mon yy hh24:mi:ss')
         || v_crlf
         || 'From: '
         || p_sendaddr
         || v_crlf
         || 'To: '
         || p_rcptaddr
         || v_crlf
         || 'Cc: '
         || p_ccaddr
         || v_crlf
         || 'Subject: '
         || p_subject
         || v_crlf
         || p_body
         || v_crlf;
      UTL_SMTP.data (mail_conn, v_mesg);
      UTL_SMTP.quit (mail_conn);
      RETURN (TRUE);
   EXCEPTION
      WHEN OTHERS
      THEN
         p_error_msg := SQLERRM;
         RETURN (FALSE);
   END sendmail_from_plsql;


call the above procedure using the below code:

Here v_mail_sent must be a boolean datatype



DECLARE
   v_errmsg   VARCHAR2 (2000);
   v_true boolean;
BEGIN
  v_true:= ctar_utility_pkg.sendmail_from_plsql (' sender email_id',
                        'To email_id',
                        'CC email_id',
                        'Hello , this is a test mail',
                        'Hello , this is a test mail',
                        v_errmsg);
END;



2. The below code is used to send email to multiple recipients:



 FUNCTION address_email (p_string IN VARCHAR2, p_recipients IN array)
      RETURN VARCHAR2
   IS
      l_recipients   LONG;
   BEGIN
      FOR i IN 1 .. p_recipients.COUNT
      LOOP
         UTL_SMTP.rcpt (mail_conn, p_recipients (i));

         IF (l_recipients IS NULL)
         THEN
            l_recipients := p_string || p_recipients (i);
         ELSE
            l_recipients := l_recipients || ', ' || p_recipients (i);
         END IF;
      END LOOP;

      RETURN l_recipients;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            ' address_email  error message is  ' || SQLERRM);
   END address_email;
  /*********************************************************************************
         Function Name: sendmail_to_multireceipents
            Description: This function sends and Email through the system mail host
                         to multiple address in the "p_rcptaddr" input parameter.
           Input Params: p_sendaddr : Email address of the sender.
                         p_to : Long List of Email address of the receipients.
                         p_cc :Long list of Email address of the cc addresses.
                         p_bcc :Long list of Email address of the bcc addresses.
                         p_subject: Subject title of the Email message.
                         p_body   : Body of the mail message.
  *******************************************************************************/


   PROCEDURE sendmail_to_multireceipents (
      p_sendaddr   IN VARCHAR2,
      p_to         IN array DEFAULT array (),
      p_cc         IN array DEFAULT array (),
      p_bcc        IN array DEFAULT array (),
      p_subject    IN VARCHAR2,
      p_body       IN LONG)
   AS
      l_to_list    LONG;
      l_cc_list    LONG;
      l_bcc_list   LONG;

      PROCEDURE writeData (p_text IN VARCHAR2)
      AS
      BEGIN
         IF (p_text IS NOT NULL)
         THEN
            UTL_SMTP.write_data (mail_conn, p_text || v_crlf);
         END IF;
      END;

   BEGIN
      mailhost := 'XXXXX';          --       mail host name
      mail_conn := UTL_SMTP.open_connection (mailhost, 25);

      UTL_SMTP.helo (mail_conn, mailhost);
      UTL_SMTP.mail (mail_conn, p_sendaddr);
      l_to_list := address_email ('To: ', p_to);
      l_cc_list := address_email ('Cc: ', p_cc);
      UTL_SMTP.open_data (mail_conn);
      writeData ('Date: ' || execution_date);
      writeData ('From: ' || p_sendaddr);
      writeData ('Subject: ' || NVL (p_subject, '(no subject)'));


      writeData (l_to_list);
      writeData (l_cc_list);

      UTL_SMTP.write_data (mail_conn, '' || v_crlf);
      UTL_SMTP.write_data (mail_conn, p_body);
      UTL_SMTP.close_data (mail_conn);
      UTL_SMTP.quit (mail_conn);
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            ' sendmail_to_multireceipents error message is  ' || SQLERRM);
   END sendmail_to_multireceipents;


Code to call the above procedure is :


BEGIN
   ctar_utility_pkg.sendmail_to_multireceipents (
      p_sendaddr   => 'Sender email id',
      p_to             => ctar_utility_pkg.array ('To email id's'),
      p_cc             =>  ctar_utility_pkg.array ('CC email id'),
      p_bcc            =>  NULL,
      p_subject        => 'Test Mail',
      p_body           => 'Hello , this is a test mail');
END;
/