DateDiff function

• Michael Du Bois

  SSCertifiable

  Points: 5163

  More actions

  April 28, 2005 at 4:19 am

  #555473

  Additions to documentation from above post are marked in RED.

  (Gee I just read my original post I have got to learn how to cut and paste )

   

  HTH

  Mike

  /*

  Find all anniversary dates that will fall within the next 30 days

   Condiitons

              EE must have been employed with the company at least 1 year

             

              The start date must be between today and 30 days from today

             

              Note that adding 30 days to the current date may not take you into

              a new month. Jan 1 yyyy + 30 days = Jan 31 yyyy

   

  To change the number of days change the constant marked in red. Negative Values work if you want to find the number of employees who had an  anniversary within the last 30 days           

   

               

   

   

  Returns Employee ID, Employee Start Date, The Anniversary being honored  

   

   

             

  */

   

   

  SELECT             s.EEID,

                     Convert(char(12),s.StartDate)AS "Employed On",

                     DATEDIFF ( yy , StartDate , GetDate() ) AS Anniversary

  FROM #T AS S

  WHERE             DatePart(mm,S.StartDate) = DatePart(mm,GetDate())

                          AND   

                          DatePart(yy,S.StartDate)< DatePart(yy,GetDate())

                          AND

                          DatePart(dd,S.Startdate) >= DatePart(dd,GetDate())

              OR

              DatePart(mm,S.StartDate) = DatePart(mm,GetDate() +30)--change here

                          AND   

                          DatePart(yy,S.StartDate)< DatePart(yy,GetDate())