如何通过比较给定日期的两个列(DataType:datetime)从表中获取代码 [英] How to Fetch Code from a Table by comparing two Columns(DataType:datetime) with respect to a given Date

问题描述

嗨！

我有一个财政年度表.

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

我想获取有关Date的代码.例如Date:5/2/2011(2011年5月2日).
输出应为:1112

请编写/更正查询.

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.

推荐答案

您的查询似乎还可以，但是如果使用SQL Server，则需要检查ms sql服务器上的日期格式.如果它与本地系统日期格式不同，则结果应该是错误的.可能您需要设置日期格式 [

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

并查看输出;)

我一直在等待接受我的答案，但是它不会来:(因此，我已决定检查原因...
终于我找到了解决方法，但是我不知道MS SQL Server行为不同的原因...

我的SQL SERVER 2005 EE设置:
语言: ENGLISH(美国)
归类: POLISH_CI_AS
日期格式: ymd

测试代码:

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

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)

这只是一个简单的逻辑问题，您在查询中倒置了比较运算符(日期不能在2011年4月1日之前，并且不能在2012年3月31日之后).

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

这篇关于如何通过比较给定日期的两个列(DataType:datetime)从表中获取代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！