How to Fetch Code from a Table by comparing two Columns(DataType:datetime) with respect to a given Date

Your query seems to be OK, but if you use SQL Server, you need to check date format on ms sql server. If it differ from your local system date format, the result should be wrong. Probably, you need to SET DATEFORMAT[^]
 
Use simple trick:

SET DATEFORMAT mdy;
GO
DECLARE @datevar DATETIME
SET @datevar = '5/2/2011';
SELECT @datevar AS [Date], DATENAME(mm,@datevar) AS [DayName];
GO

and see the output
 
I was waiting for accept for my answer, but it won't to come So, i've made decision to check why...
Finally i have found a solution, but i don't know the reasons of different behavior of MS SQL Server...
 
My SQL SERVER 2005 EE settings:
Language: ENGLISH (USA)
Collation: POLISH_CI_AS
Date format: ymd
 
Testing code:

IF NOT OBJECT_ID(N'#FYear') IS NULL DROP TABLE #FYear
CREATE TABLE #FYear (
			Code int NOT Null,
			D1 DateTime NULL,
			D2 DateTime NULL)
GO
 
SET DATEFORMAT mdy;
GO
 
INSERT INTO #FYear ([Code], [D1], [D2])
	VALUES(1011, '4/1/2010', '3/31/2011')
GO
INSERT INTO #FYear ([Code], [D1], [D2])
	VALUES(1112, '4/1/2011', '3/31/2012')
GO
INSERT INTO #FYear ([Code], [D1], [D2])
	VALUES(1213, '4/1/2012', '3/31/2013')
GO
 

DECLARE @dDate NVARCHAR(10)
 
SET @dDate='5/2/2011'
 
SELECT * FROM #FYear
 
SELECT CONVERT(DATETIME, @dDate, 101) AS [Date]
 
SELECT [Code] 
FROM #FYear 
WHERE ([D1] >=  CONVERT(DATETIME, @dDate , 101)) AND ([D2] <= CONVERT(DATETIME, @dDate , 101))
 
-- WORKING QUERY! 
SELECT [Code]
FROM #FYear 
WHERE CONVERT(INT, @dDate - [D1]) >=0 AND CONVERT(INT, @dDate - [D2])<=0
GO
 
DROP TABLE #FYear
GO

 
Sorry for my language...