4 Replies Latest reply: May 3, 2013 11:03 AM by Frank Kulash RSS

    birthMonth-String to Date Conversion -Get this month's data

    user1758353
      Hi,

      We would like the get the results for this month 's birthmonth records from table . If this month is May(05) then I need to get May month Birthmonth records from table.

      { Code  }

      Create table birthmonth ( birthmonth Varchar2(10), customernumber number);

      INSERT INTO BIRTHMONTH ('1', 100);

      INSERT INTO BIRTHMONTH ('2', 101);
      INSERT INTO BIRTHMONTH ('3', 102);
      INSERT INTO BIRTHMONTH ('41', 103);
      INSERT INTO BIRTHMONTH ('5', 104);
      INSERT INTO BIRTHMONTH ('10', 105);

      INSERT INTO BIRTHMONTH ('11', 106);

      {code }
        • 1. Re: birthMonth-String to Date Conversion -Get this month's data
          Frank Kulash
          Hi,
          user1758353 wrote:
          Hi,

          We would like the get the results for this month 's birthmonth records from table . If this month is May(05) then I need to get May month Birthmonth records from table.
          Here's one way:
          SELECT  *
          FROM     birthmonth
          WHERE     birthmonth     = TO_CHAR ( SYSDATE
                                  , 'fmMM'
                                )
          ;
          SYSDATE returns today's date.
          TO_CHAR (SYSDATE, 'MM') returns the current month (a 2-character string, with a leading '0' for most months).
          TO_CHAR (SYSDATE, 'fmMM') returns the current month (a 1- or 2-character string, without a leading '0').
          { Code  }

          Create table birthmonth ( birthmonth Varchar2(10), customernumber number);

          INSERT INTO BIRTHMONTH ('1', 100);

          INSERT INTO BIRTHMONTH ('2', 101);
          INSERT INTO BIRTHMONTH ('3', 102);
          INSERT INTO BIRTHMONTH ('41', 103);
          INSERT INTO BIRTHMONTH ('5', 104);
          INSERT INTO BIRTHMONTH ('10', 105);

          INSERT INTO BIRTHMONTH ('11', 106);

          {code }
          Thanks for posting that, but none of the INSERT statements work; they need the VALUES keyword.
          Always test (and, if necessary, correct) the CREATE TABLE and INSERT statements before you post them.

          Does it really make sense to store just the month? Most often, people store the entire birth_date in a DATE column. It's easy to use EXTRACT or TO_CHAR to get just the month from a DATE.
          Given that you really want to use a VARCHAR column, making it 10 characters long is just inviting trouble. Why not make it 2 characters long? Also, add a CHECK constraint to make sure invalid months, such as '41' above, don't get entered.
          • 2. Re: birthMonth-String to Date Conversion -Get this month's data
            jeneesh
            You could write a query as
            select *
            from birthmonth
            where birthmnoth = to_char(sysdate,'fmmm');
            • 3. Re: birthMonth-String to Date Conversion -Get this month's data
              user1758353
              Thanks Frank and Jeenesh for your help
              • 4. Re: birthMonth-String to Date Conversion -Get this month's data
                Frank Kulash
                Hi,

                Do you sometimes (but not always) have a leading '0' in birthmonth? For example:
                INSERT INTO birthmonth (birthmonth, customernumber)
                                VALUES ('05',          987);
                If so, you can use LTRIM to remove it in the solution above, or use LPAD instead of 'fm', like this:
                SELECT  *
                FROM     birthmonth
                WHERE     LPAD ( birthmonth
                          , 2
                          , '0'
                          )          = TO_CHAR (SYSDATE, 'MM')
                ;