Get employee names on the basis of Date
Today, without time, is:

DECLARE @d SMALLDATETIME;
SET @d = DATEADD(DAY, GETDATE(), DATEDIFF(DAY, '19000101', GETDATE());

-- when you move past SQL Server 2005, which you should, you can use the much tidier:
-- DECLARE @d DATE = SYSDATETIME();


Then to move to the first day of this month:

SET @d = DATEADD(DAY, 1-DAY(@d), @d);


Now to get all employees who started within the month 6 months earlier, you can say:

DECLARE @d SMALLDATETIME;

SET @d = DATEADD(DAY, 1-DAY(GETDATE()), DATEDIFF(DAY, '19000101', GETDATE()));    

SELECT ... 
  WHERE Dt_Of_Join &gt;= DATEADD(MONTH,-6,@d)
    AND Dt_Of_Join &lt;  DATEADD(MONTH,-5,@d);
-- I assume you'll have other filters to make
-- sure employee is still employed, etc.


The smart way to do this would be through a parameter, which falls back to today if no date is supplied.

CREATE PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;
  SET @dt = COALESCE(@dt, GETDATE());
  SET @dt = DATEADD(DAY, 1-DAY(@dt), DATEDIFF(DAY, '19000101', @dt));

  SELECT ... 
  WHERE Dt_Of_Join &gt;= DATEADD(MONTH,-6,@dt)
    AND Dt_Of_Join &lt;  DATEADD(MONTH,-5,@dt);

  -- for debugging only:
  PRINT '&gt;= ' + CONVERT(CHAR(10), DATEADD(MONTH,-6,@dt), 120);
  PRINT '&lt;  ' + CONVERT(CHAR(10), DATEADD(MONTH,-5,@dt), 120);
END
GO


(Now it's also very easy to test that this works for any date, including February 29th in leap years...)

Here is the illogical way to do it in one line (I have absolutely no clue why you think this is a valid or important requirement):

ALTER PROCEDURE dbo.GetNamesByDate -- not the name you should use
  @dt SMALLDATETIME = NULL
AS
BEGIN
  SET NOCOUNT ON;

  SELECT ... WHERE Dt_Of_Join &gt;= DATEADD(MONTH,-6,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE())))) AND Dt_Of_Join &lt;  DATEADD(MONTH,-5,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE()))));
END
GO


Oh yeah, that's much easier to read and troubleshoot. Also, it didn't save you any characters, it's actually more... &lt;/facepalm&gt;