面对SQL Query下面执行的问题 [英] Facing issue to execute below SQL Query

问题描述

Hi Team，

我正在执行以下查询

Hi Team ,
I am Executing the below query

--DECLARE @dateFrom DateTime
--DECLARE @dateTo DateTime
declare @tbl TABLE (
   ID INT identity(1,1),
   Name VarCHar(128),
   DateFrom Datetime,
   DateTo Datetime
)
INSERT INTO @tbl VALUES('A','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('B','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('C','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('D','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('E','2010-01-01','2015-01-01')

SELECT CONVERT(char(10),DateTo,103),CONVERT(char(10),DateFrom,111),CONVERT(char(10),getdate(),111)
FROM @tbl WHERE Convert(char(10),getdate(),103) BETWEEN CONVERT(char(10),DateFrom,103) AND CONVERT(char(10),DateTo,103)

请告诉我上面的查询是否合适。

Please let me know that my above query is OK or not.

推荐答案

如果需要的结果是获取getDate在DateFrom和DateTo之间的记录。

然后执行以下操作：

If the result required is to get records where the getdate is to be between DateFrom and DateTo.
Then do something like this:

select 
	*,
	CONVERT(char(10),DateTo,103) DTo,
	CONVERT(char(10),DateFrom,111) DFrom,
	CONVERT(char(10),getdate(),111) DNow
from @tbl
where DateTo > getdate() 
and DateFrom < getdate()
;

重新访问：

查询不起作用的原因是两者之间是比较char（s）和不是datetime（s）。

Revisiting:
The reason the query is not working is the between is comparing char(s) and not datetime(s).

SELECT 
	Case When (Convert(char(10),getdate(),103) > CONVERT(char(10),DateFrom,103)) 
		Then 'getdate GT' 
		Else 'getdate not GT' 
	End CharGetDateGTDateFrom,
	Case When (Convert(char(10),getdate(),103) > CONVERT(char(10),DateTo,103)) 
		Then 'getdate GT' 
		Else 'getdate not GT' 
	End CharGetDateGTDateTo,
	Case When (getdate() > DateFrom) 
		Then 'getdate GT' 
		Else 'getdate not GT' 
	End GetDateGTDateFrom,
	Case When (getdate() > DateTo) 
		Then 'getdate GT' 
		Else 'getdate not GT' 
	End GetDateGTDateTo
FROM @tbl ;

/* result
CharGetDateGTDateFrom	CharGetDateGTDateTo		GetDateGTDateFrom		GetDateGTDateTo
getdate GT				getdate GT				getdate GT				getdate not GT
*/

从比较CHAR值的上述语句的结果可以看出getdate大于（GT）DateFrom和DateTo。

当比较比较DATETIME值时，getdate大于DateFrom且不大于DateTo。

只做如果需要进行DATETIME比较，则不要在where子句中转换为char。

另外解决方案3也不错：）

As can be seen from the result of the above statement in the comparison of the CHAR values the getdate is greater than (GT) the DateFrom and DateTo.
Whereas when the comparison is comparing DATETIME values the getdate is greater than DateFrom and not greater than DateTo.
Just do not convert to char in the where clause if a DATETIME comparison is required.
Also solution 3 is good :)

尝试这样，你将char传递给datetime，所以试试这样，

Try like this,you are passing char to datetime,So try like this,

declare @tbl TABLE (
   ID INT identity(1,1),
   Name VarCHar(128),
   DateFrom Datetime,
   DateTo Datetime
)
INSERT INTO @tbl VALUES('A','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('B','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('C','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('D','2010-01-01','2015-01-01')
INSERT INTO @tbl VALUES('E','2010-01-01','2015-01-01')
 
SELECT CONVERT(char(10),DateTo,103),CONVERT(char(10),DateFrom,111),CONVERT(char(10),getdate(),111)
FROM @tbl WHERE getdate() BETWEEN DateFrom AND DateTo

大家好，

i我只是问我的代码是错的，请解释原因？

Hi All ,
i am just asking is my code is wrong then please explain why ?

这篇关于面对SQL Query下面执行的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！