chef423 - Wednesday, November 22, 2017 8:51 AM

Jeff Moden - Tuesday, November 21, 2017 8:46 PM

If finding rows by birth month without regard to year or day is something important, it's probably worthwhile to materialize the birth month in the table using a persisted computed column.  Like this....


 CREATE TABLE dbo.birthdays
        (
         cust_id    INTEGER         NOT NULL PRIMARY KEY
        ,cust_fname NVARCHAR(50)    NOT NULL
        ,cust_lname NVARCHAR(50)    NOT NULL
        ,cust_dob   DATETIME        NOT NULL
        ,cust_dobmo AS DATEPART(mm,cust_dob) PERSISTED --<----<<< Added this column    
        )
;


Then the code become trivial for such things.  For example...


DECLARE @pSomeDate DATETIME = GETDATE(); --Could be a parameter for a Stored Procedure or iTVF

 SELECT *
   FROM dbo.birthdays
  WHERE cust_dobmo = DATEPART(mm,@pSomeDate)
;

Thanks Jeff. The original table I get the Customer data from is dbo.Customers

So I cannot replace birthdays with that name is says the table is already created.