birth day check sql

[Former Member]

hai experts i have a table employee in sql anywhere database which contains emp_id datatype integer, emp_name datatype varchar(50) and emp_dateofbirth datatype date. i want advance happy birth day wish to the employees that their birth day coming in one or two days how to write a sql or stored procedure in sql anywhere to select employees who have birthdays that are upcoming one or two days regards kumar

[Breck_Carter]

CREATE TABLE employee (
   emp_id integer,
   emp_name varchar(10),
   emp_dateofbirth date );
INSERT employee VALUES ( 1, 'Jack', '1966 12 30' );
INSERT employee VALUES ( 2, 'Jill', '1977 12 31' );
INSERT employee VALUES ( 3, 'Went', '1988 01 01' );
INSERT employee VALUES ( 4, 'Hill', '1999 01 02' );
INSERT employee VALUES ( 5, 'Pail', '2001 01 03' );
COMMIT;

SELECT CAST ( '2018 12 31' AS DATE ) AS today,
       employee.*,
       CAST ( STRING ( DATEFORMAT ( CURRENT DATE, 'yyyy' ), 
                       DATEFORMAT ( emp_dateofbirth, 'mmdd' ) ) AS DATE ) AS this_birthday,
       CAST ( STRING ( CAST ( DATEFORMAT ( CURRENT DATE, 'yyyy' ) AS INTEGER ) + 1, 
                       DATEFORMAT ( emp_dateofbirth, 'mmdd' ) ) AS DATE ) AS next_birthday 
  FROM employee 
 WHERE DATEDIFF ( DAY, today, this_birthday ) BETWEEN 0 AND 2
    OR DATEDIFF ( DAY, today, next_birthday ) BETWEEN 0 AND 2
 ORDER BY emp_id;

TRUNCATE TABLE employee;
INSERT employee VALUES ( 1, 'Jack', '1966 04 28' );
INSERT employee VALUES ( 2, 'Jill', '1977 04 29' );
INSERT employee VALUES ( 3, 'Went', '1988 04 30' );
INSERT employee VALUES ( 4, 'Hill', '1999 05 01' );
INSERT employee VALUES ( 5, 'Pail', '2001 05 02' );
COMMIT;

SELECT CAST ( '2018 04 29' AS DATE ) AS today,
       employee.*,
       CAST ( STRING ( DATEFORMAT ( CURRENT DATE, 'yyyy' ), 
                       DATEFORMAT ( emp_dateofbirth, 'mmdd' ) ) AS DATE ) AS this_birthday,
       CAST ( STRING ( CAST ( DATEFORMAT ( CURRENT DATE, 'yyyy' ) AS INTEGER ) + 1, 
                       DATEFORMAT ( emp_dateofbirth, 'mmdd' ) ) AS DATE ) AS next_birthday 
  FROM employee 
 WHERE DATEDIFF ( DAY, today, this_birthday ) BETWEEN 0 AND 2
    OR DATEDIFF ( DAY, today, next_birthday ) BETWEEN 0 AND 2
 ORDER BY emp_id;

today           emp_id emp_name   emp_dateofbirth this_birthday next_birthday 
---------- ----------- ---------- --------------- ------------- ------------- 
2018-12-31           2 Jill       1977-12-31      2018-12-31    2019-12-31    
2018-12-31           3 Went       1988-01-01      2018-01-01    2019-01-01    
2018-12-31           4 Hill       1999-01-02      2018-01-02    2019-01-02    

today           emp_id emp_name   emp_dateofbirth this_birthday next_birthday 
---------- ----------- ---------- --------------- ------------- ------------- 
2018-04-29           2 Jill       1977-04-29      2018-04-29    2019-04-29    
2018-04-29           3 Went       1988-04-30      2018-04-30    2019-04-30    
2018-04-29           4 Hill       1999-05-01      2018-05-01    2019-05-01 

[VolkerBarth]

Here's one sample from the SQL Anywhere demo database, which happens to have a table "employees" with a "BirthDate" column.

It calculates the datepart "dayofyear" for the current day and for the birthday - for this year, if that day is still coming, or for next year:

select EmployeeID, Surname, GivenName, BirthDate,
   datepart(dayofyear, current date) as CurrentDay,
   datepart(dayofyear, ymd(year(current date), month(BirthDate), day(BirthDate))) as ThisYearsBirthDay,
   if ThisYearsBirthDay < CurrentDay then
        -- if this year's birthday has passed, calculate the "dayofyear" of next year's birthday
        -- and add this year's number of days (365 or 366)
          datepart(dayofyear, ymd(year(current date) + 1, month(BirthDate), day(BirthDate)))
        + datepart(dayofyear, dateadd(dd, -1, ymd(year(current date) + 1, 1, 1)))
   else ThisYearsBirthDay end if as NextBirthDay,   
   NextBirthDay - CurrentDay as DaysUntilBirthday
from employees
where DaysUntilBirthday between 0 and 20
order by DaysUntilBirthday, Surname, GivenName;

lists

EmployeeID,Surname,GivenName,BirthDate,ThisYearsBirthDay,CurrentDay,NextBirthDay,DaysUntilBirthday
1142,Clark,Alison,1957-05-04,124,120,124,4
757,Higgins,Denis,1968-05-12,132,120,132,12
160,Breault,Robert,1947-05-13,133,120,133,13
278,Melkisetian,Terry,1966-05-17,137,120,137,17

It's probably not the shortest form (using datediff with number of days would probably be shorter) but I guess it's a good starting point.