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))
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...