PL/SQL checking if number is Integer

Question

I want to check my employee table if there is any employee who works as an employee for exactly one year or two years, or three years, ... The Problem for me now is that i dont know how i can check if a number is an integer or not.

CREATE OR REPLACE PROCEDURE jubilar
IS
  v_cur_date DATE := TO_DATE('03-JAN-2012');
  v_cur_year NUMBER;
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
  v_hire_date employees.hire_date%TYPE;
  CURSOR c_emp_cursor IS
    SELECT first_name, last_name, hire_date FROM employees;
BEGIN
  OPEN c_emp_cursor;
  LOOP
    FETCH c_emp_cursor INTO v_first_name, v_last_name, v_hire_date;
    EXIT WHEN c_emp_cursor%NOTFOUND;
    v_cur_year := round((v_cur_date - v_hire_date) / 365, 1);
    IF v_cur_year ???
      DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ist heute ' || v_cur_year || ' Jahre im Unternehmen tätig.');
    END IF;
  END LOOP;
  CLOSE c_emp_cursor;
END jubilar;
/

At this line

IF v_cur_year ??

I need to check if v_cur_year is an integer or not. Because if it is the employee works for exactly X year as an employee. And i need to know that.

EDIT:

I tried this:

CREATE OR REPLACE PROCEDURE jubilar
IS
  v_cur_date DATE := TO_DATE('03/01/12', 'dd/mm/yy');
  v_cur_year NUMBER;
  v_cur_year_temp VARCHAR2(100);
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
  v_hire_date employees.hire_date%TYPE;
  CURSOR c_emp_cursor IS SELECT first_name, last_name, hire_date FROM employees;
BEGIN
  OPEN c_emp_cursor;
  LOOP
    FETCH c_emp_cursor INTO v_first_name, v_last_name, v_hire_date;
    EXIT WHEN c_emp_cursor%NOTFOUND;

    v_cur_year := round((v_cur_date - v_hire_date) / 365, 1);
    v_cur_year_temp := TO_CHAR(v_cur_year);

    IF REGEXP_COUNT(v_cur_year_temp, ',') = 0 THEN
      DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ist heute ' || v_cur_year || ' Jahre im Unternehmen tätig.' || TO_CHAR(v_hire_date));
    END IF;
  END LOOP;
  CLOSE c_emp_cursor;
END jubilar;
/

But it gives me wrong persons with hire_date for example 17/01/2005

I also tried this:

CREATE OR REPLACE PROCEDURE jubilar
IS
  v_cur_date DATE := TO_DATE('03/01/12', 'dd/mm/yy');
  v_cur_year NUMBER;
  v_first_name employees.first_name%TYPE;
  v_last_name employees.last_name%TYPE;
  v_hire_date employees.hire_date%TYPE;
  CURSOR c_emp_cursor IS SELECT first_name, last_name, hire_date FROM employees;
BEGIN
  OPEN c_emp_cursor;
  LOOP
    FETCH c_emp_cursor INTO v_first_name, v_last_name, v_hire_date;
    EXIT WHEN c_emp_cursor%NOTFOUND;

    v_cur_year := round((v_cur_date - v_hire_date) / 365, 1);

    IF trunc(v_cur_year) = v_cur_year THEN
      DBMS_OUTPUT.PUT_LINE(v_first_name || ' ' || v_last_name || ' ist heute ' || v_cur_year || ' Jahre im Unternehmen tätig.' || TO_CHAR(v_hire_date));
    END IF;
  END LOOP;
  CLOSE c_emp_cursor;
END jubilar;
/

But it gives me wrong persons with hire_date for example 17/01/2005

You can use if trunc(v_cur_year) = v_cur_year to check if the number is integer or not. But how will you handle leap years? — Noel
– Noel, Commented Jan 12, 2014 at 10:33
This is not a solution :(. The actual date is 03-JAN-2012. With IF trunc(v_cur_year) = v_cur_year i get 13-JAN-2001 für 11 years, 24-DEC-2005 for 6 years, 14-Jan-2007 for 5 years, .... The only correct person i get is 03.01.06 for 6 years. — user1882812
– user1882812, Commented Jan 12, 2014 at 11:09
You may want to give some thought to the proper handling of a hire date of 29-FEB-2012. Share and enjoy. — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Jan 13, 2014 at 0:07

user3187076 · Accepted Answer · 2014-01-12 10:37:02Z

1

This may help you ..

   DECLARE
   d1    DATE := TO_DATE ('01/12/12', 'mm/dd/yy');
   d2    DATE := SYSDATE;
   n1    NUMBER;
   chk   VARCHAR2 (100);
    BEGIN
   n1 := ROUND ( (d2 - d1) / 365, 1);
   DBMS_OUTPUT.put_line (n1);
   chk := TO_CHAR (n1);
   DBMS_OUTPUT.put_line (chk);

   IF REGEXP_COUNT (chk, '.') != 0
    THEN
      ---YOUR LOGIC
    END IF;
END;

answered Jan 12, 2014 at 10:37

user3187076

361 bronze badge

Sign up to request clarification or add additional context in comments.

4 Comments

user1882812 Over a year ago

No it does'nt. IF REGEXP_COUNT(chk, '.') != 0 is true for 01/12/12 and for 02/12/12. So its not correct.

user3187076 Over a year ago

So what will be your incoming date format?? and here you will look for integer or not.. the best way is to convert to character and check as we do in other programming languages..

user3187076 Over a year ago

Since the date is dynamic you must use to_date and you should check for '.' not for ','

user1882812 Over a year ago

That cannot be cause there is no '.' in the date, there is just a ',' in it. however.. problem is solved

score 0 · Accepted Answer · 2014-01-12 14:03:08Z

0

To check for employees who are employed exactly two years at e.g. January 14th, you can use this:

select *
from employees
where months_between(DATE '2014-01-14', trunc(hire_date)) / 12 = 2;

^{(I prefer using ANSI date literals over the to_date() function. Works on multiple DBMS and is less typing)}

months_between(...) / 12 will result in a fractional value if it's not exactly one year and therefore the comparison with a non-fractional value will fail.

Just change the comparison date (DATE '2014-01-14') and the condition ( =2) to accommodate for other dates or durations.

SQLFiddle example: http://sqlfiddle.com/#!4/27c3d/4

edited Jan 12, 2014 at 14:03

answered Jan 12, 2014 at 12:09

user330315

1 Comment

user1882812 Over a year ago

It works but i think you mean months_between(DATE '2014-01-14', hire_date) / 12 = 2;

Wernfried Domscheit · Accepted Answer · 2014-01-12 14:13:13Z

0

I would do it like this:

with y as 
   (select trunc(ADD_MONTHS(SYSDATE, -12 * LEVEL)) as the_date, LEVEL as anniversary
   from dual
   connect by LEVEL <= 6)
select first_name, last_name, hire_date, anniversary, the_date
   join y on  the_date = hire_date

answered Jan 12, 2014 at 14:13

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Collectives™ on Stack Overflow

PL/SQL checking if number is Integer

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related