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

asked 29 Apr, 04:01

ghkumarchd's gravatar image

ghkumarchd
253
accept rate: 0%


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.

permanent link

answered 30 Apr, 03:49

Volker%20Barth's gravatar image

Volker Barth
32.1k327469688
accept rate: 32%

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 
permanent link

answered 30 Apr, 07:51

Breck%20Carter's gravatar image

Breck Carter
27.6k472628905
accept rate: 21%

edited 30 Apr, 16:31

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×83

question asked: 29 Apr, 04:01

question was seen: 106 times

last updated: 30 Apr, 16:31