1 2 Previous Next 21 Replies Latest reply: Dec 8, 2012 1:41 AM by jeneesh RSS

    how to find the Birthday of employee in SQL

    971420
      Hi All,

      Please help me to write a sql query to find the birthday of employee of employee
      7 days before and 7 days after.

      Thanks
      Raju
        • 1. Re: how to find the Birthday of employee in SQL
          jeneesh
          with t as 
          (select to_date('12111962','ddmmyyyy') dob from dual union all
           select to_date('11111962','ddmmyyyy') dob from dual union all
           select to_date('05111962','ddmmyyyy') dob from dual union all
           select to_date('19111962','ddmmyyyy') dob from dual union all
           select to_date('02111962','ddmmyyyy') dob from dual union all
           select to_date('25111962','ddmmyyyy') dob from dual )
          select dob,floor(months_between(trunc(sysdate),dob)/12) age,
                 floor(months_between(trunc(sysdate-7),dob)/12) age_b4_7,
                 floor(months_between(trunc(sysdate+7),dob)/12) age_aftr_7
          from t;
          
          DOB       AGE AGE_B4_7 AGE_AFTR_7
          --------- --- -------- ----------
          12-NOV-62  50       49         50 
          11-NOV-62  50       49         50 
          05-NOV-62  50       50         50 
          19-NOV-62  49       49         50 
          02-NOV-62  50       50         50 
          25-NOV-62  49       49         49 
          
           6 rows selected 
          • 2. Re: how to find the Birthday of employee in SQL
            AlbertoFaenza
            Hi,

            Please read SQL and PL/SQL FAQ
            Next time post sample data, logic and expected output.

            Suppose that you have a list of employees and their birthday, this will list employes having their birthday within -7 and +7 from sysdate.

            Regards.
            WITH myemps AS
            (
              SELECT 7369 empno, 'SMITH'  empname, TO_DATE('31/08/1972', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
              SELECT 7499 empno, 'ALLEN'  empname, TO_DATE('09/11/1980', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
              SELECT 7521 empno, 'WARD'   empname, TO_DATE('15/11/1976', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
              SELECT 7566 empno, 'JONES'  empname, TO_DATE('30/11/1985', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
              SELECT 7654 empno, 'MARTIN' empname, TO_DATE('15/12/1987', 'DD/MM/YYYY') birthdate FROM DUAL
            )
            SELECT * 
              FROM myemps
             WHERE TO_DATE(TO_CHAR(birthdate,'MMDD'),'MMDD') 
                   BETWEEN trunc(SYSDATE-7) AND trunc(SYSDATE+7);
            
                 EMPNO EMPNAME BIRTHDATE
            ---------- ------- ---------
                  7499 ALLEN   09-NOV-80
                  7521 WARD    15-NOV-76       
            Regards.
            Al
            • 3. Re: how to find the Birthday of employee in SQL
              naviNkumArG
              Hi Raju,

              I hope the below sql is fulfill your needs,

              SELECT empno, empname,
              CASE
              WHEN TO_DATE (birth_date) + 7 = TRUNC (SYSDATE)
              THEN 'Employees having birth day '
              || birth_date
              || ' befoe Seven Days '
              END seven_days_before
              FROM employee
              UNION ALL
              SELECT empno, empname,
              CASE
              WHEN TO_DATE (birth_date) - 7 = TRUNC (SYSDATE)
              THEN 'Employees having birth day '
              || birth_date
              || ' after Seven Days '
              END seven_days_after
              FROM employee;

              do reponds to the posts.

              Regards,
              Navin Kumar G
              • 4. Re: how to find the Birthday of employee in SQL
                Gurujothi
                Hi Raju,

                Try this,
                select * from your_table_name
                where date_field between sysdate - 7 and sysdate +7
                Regards,
                Guru
                • 5. Re: how to find the Birthday of employee in SQL
                  971420
                  Thanks a lot guru :)
                  it's working now
                  • 6. Re: how to find the Birthday of employee in SQL
                    AlbertoFaenza
                    968417 wrote:
                    Thanks a lot guru :)
                    it's working now
                    Hi,

                    please mark the question as answered when you are satisfied with the answers.

                    Regards.
                    Al
                    • 7. Re: how to find the Birthday of employee in SQL
                      Gurujothi
                      Hi,

                      Please mark the thread as answered so members can focus on other threads
                      • 8. Re: how to find the Birthday of employee in SQL
                        ranit B
                        WHERE TO_DATE(TO_CHAR(birthdate,'MMDD'),'MMDD') 
                        BETWEEN trunc(SYSDATE-7) AND trunc(SYSDATE+7);
                        Alberto,

                        Can you please explain this WHERE clause?
                        I'm not getting why you are using TO_CHAR & then again TO_DATE, coz the column is already DATE..??
                        • 9. Re: how to find the Birthday of employee in SQL
                          BluShadow
                          ranit B wrote:
                          WHERE TO_DATE(TO_CHAR(birthdate,'MMDD'),'MMDD') 
                          BETWEEN trunc(SYSDATE-7) AND trunc(SYSDATE+7);
                          Alberto,

                          Can you please explain this WHERE clause?
                          I'm not getting why you are using TO_CHAR & then again TO_DATE, coz the column is already DATE..??
                          He's doing it to remove the year from the date of birth and bring the month and day part into this year...
                          SQL> select to_char(date '1980-04-13','MMDD') from dual;
                          
                          TO_C
                          ----
                          0413
                          
                          SQL> select to_date('0413','MMDD') from dual;
                          
                          TO_DATE('0413','MMDD
                          --------------------
                          13-APR-2012 00:00:00
                          That way, you can see what birthdays are falling in the current year regardless of the year the person was actually born.
                          • 10. Re: how to find the Birthday of employee in SQL
                            Frank Kulash
                            Hi, Al,

                            Watch out for special cases when SYSDATE is in the first or last week of a year.

                            Lets modify the sample data so we have some birthdays (like those for SMITH and ALLEN) near the beginning of the year:
                            CREATE TABLE myemps AS
                            (
                              SELECT 7369 empno, 'SMITH'  empname, TO_DATE('30/12/1972', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
                              SELECT 7499 empno, 'ALLEN'  empname, TO_DATE('02/01/1980', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
                              SELECT 7521 empno, 'WARD'   empname, TO_DATE('15/11/1976', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
                              SELECT 7566 empno, 'JONES'  empname, TO_DATE('30/11/1985', 'DD/MM/YYYY') birthdate FROM DUAL UNION ALL
                              SELECT 7654 empno, 'MARTIN' empname, TO_DATE('15/12/1987', 'DD/MM/YYYY') birthdate FROM DUAL
                            );
                            Since it's hard to change SYSDATE, lets use a substitution variable
                            DEFINE     today     = "TO_DATE ('06/01/2012', 'DD/MM/YYYY')"
                            for testing.

                            You suggested:
                            SELECT * 
                              FROM myemps
                             WHERE TO_DATE(TO_CHAR(birthdate,'MMDD'),'MMDD') 
                                   BETWEEN trunc(&today-7) AND trunc(&today+7);
                            which works great for most values of &today, but in this case (&today = January 6, 2012), the last line becomes:
                            old   4:        BETWEEN trunc(&today-7) AND trunc(&today+7)
                            new   4:        BETWEEN trunc(TO_DATE ('06/01/2012', 'DD/MM/YYYY')-7) AND trunc(TO_DATE ('06/01/2012', 'DD/MM/YYYY')+7)
                            that is, birthdate in this year must be between December 30, 2011 and January, 13, 2012. But SMITH's birthday this year is December 30, 2012, not 2011, so the query only finds ALLEN.

                            I suggest something like:
                            WITH     target_days     AS
                            (
                                 SELECT     TO_CHAR ( &today + LEVEL - 8
                                           , 'DD/MM'
                                           ) AS target_day
                                 FROM     dual
                                 CONNECT BY     LEVEL     <= 15
                            )
                            SELECT  m.*
                            FROM     myemps          m
                            JOIN     target_days  t  ON  t.target_day = TO_CHAR ( m.birthdate
                                                                         , 'DD/MM'
                                                             )
                            ;
                            • 11. Re: how to find the Birthday of employee in SQL
                              ranit B
                              SQL> select to_date('0413','MMDD') from dual;
                              
                              TO_DATE('0413','MMDD
                              --------------------
                              13-APR-2012 00:00:00
                              Thanks Blu.
                              But 1 doubt over here.

                              How does TO_DATE work here, given only MM & DD....? From where it takes the year & time values?

                              I'm always confused with the exact purpose & proper usage of TO_DATE & TO_CHAR.
                              I've read many articles on this but not able to use properly.

                              I know like - TO_DATE is used for converting a String to Date type.
                              Vice-Versa is done by TO_CHAR. Also, TO_CHAR is used for Date formatting purpose.

                              Can you please explain this in some simple words?
                              Thanks.
                              • 12. Defaults in TO_DATE
                                Frank Kulash
                                Hi,
                                ranit B wrote:
                                SQL> select to_date('0413','MMDD') from dual;
                                
                                TO_DATE('0413','MMDD
                                --------------------
                                13-APR-2012 00:00:00
                                Thanks Blu.
                                But 1 doubt over here.

                                How does TO_DATE work here, given only MM & DD....? From where it takes the year & time values?
                                If you don't specify the year or the month in TO_DATE, then they default to the current values (that is, the year and month of SYSDATE). In the example above, you didn't specify a year, so it defaulted to the current year, 2012. You did specify a month, so that's the month that was used.
                                If you don't specify any of the shorter divisions of time (day, hours, minutes or seconds) then they default to the earliest possible values; that is, the default day is 1 and the default hours, minutes and seconds are all 00. In the example above, you didn't specify hours, minutes or seconds, so they all defaulted to 00. You did spoecify a day, 13, so that's the day that was used.
                                I'm always confused with the exact purpose & proper usage of TO_DATE & TO_CHAR.
                                I've read many articles on this but not able to use properly.

                                I know like - TO_DATE is used for converting a String to Date type.
                                Vice-Versa is done by TO_CHAR. Also, TO_CHAR is used for Date formatting purpose.

                                Can you please explain this in some simple words?
                                You said it pretty well already. The names of the functions remind you of what they are for.
                                Use TO_DATE to convert a character string TO a DATE .
                                Use TO_CHAR to convert a date TO a <b>CHAR</b>acter string.

                                It's rarely a good idea to nest these inside one another. That is, if you're tempted to use "TO_DATE (TO_CHAR ...)" or "TO_CHAR ( TO_DATE ...)", then think again; there's probably a better way to do whatever you need to do, without converting from one datatype to another. Oracle provides lots of funtions (as well as DATE arithmetic) for dealing wiht DATEs, and lots of functions for dealing with strings.

                                Al almost had an exception, when he said to use
                                TO_DATE(TO_CHAR(birthdate,'MMDD'),'MMDD') 
                                but, as I mentioned above, this doesn't quite do what is required in this problem.
                                • 13. Re: Defaults in TO_DATE
                                  ranit B
                                  Thanks Frank...
                                  Can you please tell me when exactly TO_CHAR is used? I don't understand the need for this... We can always declare Date variable when required. Right?
                                  • 14. Re: Defaults in TO_DATE
                                    Frank Kulash
                                    Hi,
                                    ranit B wrote:
                                    Thanks Frank...
                                    Can you please tell me when exactly TO_CHAR is used? I don't understand the need for this...
                                    You mentioned the main use earlier: use TO_CHAR to display a DATE in a particular format.

                                    A second, less frequent, use is to get a Lossy version of the DATE. For example, this thread is all about the day and month of a DATE, so various solutions have suggested using TO_CHAR to get a string value that captures the month and day, but loses (hence the term lossy ) the year, hours, minutes and seconds. Again, Oracle provides several handy DATE manipulation functions, so even when you want a lossy version of a DATE, you might use TRUNC rather than TO_CHAR. In this problem, TRUNC won't help, because TRUNC always keeps the most singnificant part, and the most significant part of the DATE, they year, is exactly what we want to lose in this case.
                                    We can always declare Date variable when required. Right?
                                    Yes, you can always declare a DATE variable (or column, or expression), and that's exactly what you should do when required. This thread gives an example of when something else is required, or at least convenient.
                                    1 2 Previous Next