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 |
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. |
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 |