SQL Server日期比较抛出错误
问题描述:
请帮助解决这个问题,独立查询如何工作,但比较失败,下面的代码,我试图只比较日期而不是时间,所以尝试这样的查询。SQL Server日期比较抛出错误
SELECT CAST(getdate() AS DATE)---'2/9/2017 12:00:00 AM'
SELECT CAST('2017/01/15' AS DATE)----'1/15/2017 12:00:00 AM'
SELECT CAST(getdate() AS DATE) > CAST('2017/01/15' AS DATE); -- Error SQLSTATE 42000
答
您需要将此条件放入上下文中。例如,像这样
SELECT case when CAST(getdate() AS DATE) > CAST('2017/01/15' AS DATE)
then 1
else 0
end
答
您可以试试DATEDIFF()来找出两个日期之间的差异。
选择DATEDIFF(DAY,(CAST( '2017年1月15日' AS DATE)),(CAST(GETDATE()AS DATE)))
答
尝试是这样的:
DECLARE @nowDate DATE
SELECT @nowDate = CAST(getdate() AS DATE)
DECLARE @otherDate DATE
SELECT @otherDate = CAST('2017/01/15' AS DATE)
SELECT DATEDIFF(DAY, @nowDate, @otherDate)
答
SELECT CAST(getdate() AS DATE)---'2/9/2017 12:00:00 AM'
SELECT CAST('2017/01/15' AS DATE)----'1/15/2017 12:00:00 AM'
SELECT IIF(CAST(getdate() AS DATE) > CAST('2017/01/15' AS DATE),1,0); --good, you must use iif (or case when) for predicates
+0
这给出了相同的错误 – sunleo
+0
IIF只存在于SQL Server 2012或更高版本中。如果使用旧版本,则必须使用CASE语句。 – reedstonefood
答
您无法在SELECT
中比较,您可以使用下面的代码来确定日期是否大于当前日期,或者是今天。
DECLARE @DATE NVARCHAR(20) = '2017/01/15'
IF (CAST(getdate() AS DATE) > CAST(@DATE AS DATE))
PRINT 'Selected date is in past'
ELSE IF (CAST(getdate() AS DATE) = CAST(@DATE AS DATE))
PRINT 'Selected date is today'
ELSE
PRINT 'Selected date is in future'
SQL Server中没有布尔数据类型 - 因此您不能将比较操作的结果作为结果集中的表达式。 –