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 >= DATEADD(MONTH,-6,@d)
AND Dt_Of_Join < 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 >= DATEADD(MONTH,-6,@dt)
AND Dt_Of_Join < DATEADD(MONTH,-5,@dt);
-- for debugging only:
PRINT '>= ' + CONVERT(CHAR(10), DATEADD(MONTH,-6,@dt), 120);
PRINT '< ' + 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 >= DATEADD(MONTH,-6,DATEADD(DAY, 1-DAY(COALESCE(@dt, GETDATE())), DATEDIFF(DAY, '19000101', COALESCE(@dt, GETDATE())))) AND Dt_Of_Join < 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... </facepalm>
2014
not2015
.DateofJoining
. because each and every month new emplyee will join, so the confimation of the employees will be differentDateOfJoining
andDt_Of_Join
are the same column, right? You're going to need to provide some sample data and desired results. This is a word problem right now, and it's hard to decipher what you mean by"dynamic" - the query you've written already seems to be dynamic based on today's date.