Click here to Skip to main content
12,406,986 members (62,384 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: SQL
Hi !

I have a Table[FYear] of Financial Year.
Code	D1	D2
1011	4/1/2010	3/31/2011
1112	4/1/2011	3/31/2012
1213	4/1/2012	3/31/2013

I want to fetch Code with respect to a Date.Like Date:5/2/2011(2 May 2011).
Output should be : 1112

Please write/Correct the Query.
select Code from FYear_Year where D1>='5/2/2011' and D2<='5/2/2011'
It does not give correct output.
Posted 7-May-12 1:50am
Updated 7-May-12 2:56am
Comments
losmac 7-May-12 15:33pm
   
See the answer (solution) no. 3. Probably i have found the solution. I'll be glad for rate my answer.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

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 Wink | ;)

I was waiting for accept for my answer, but it won't to come Frown | :( 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...
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

DECLARE @YourDate datetime
SET @YourDate = '5/2/2011'
 
SELECT
  FYear_Year.Code
FROM
  FYear_Year
WHERE
  (FYear_Year.D1 <= @YourDate) AND (FYear_Year.D2 >= @YourDate)

It is just a simple logic issue, you inverted your comparison operators in your query (no date can be before 1st April 2011 and, at the same time, after 31st March 2012).
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160730.1 | Last Updated 7 May 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100