I have two columns in my table called GXHEAD. there are PRECISETIME and DEVALERT.
我的表中有两列叫做GXHEAD。有精确的时间，有精确的距离。
Both PRECISETIME and DEVALERT hold time as varchar(255) and are in the format "DD/MM/YYYY HH:MM:SS" (e.g: "20/03/2014 14:07:11").
precision time和DEVALERT都以varchar(255)的形式保存时间，格式为“DD/MM/ yyyyyyyyyyyyyyyyyyy HH:MM:SS”(e)。g:“20/03/2014 14:07:11”)。
I would like to identify all rows where DEVALERT exceed PRECISETIME by 4 hours.
我想确认DEVALERT超过精确时间4小时的所有行。
2 个解决方案
#1
3
You can try this:
你可以试试这个:
DATEDIFF(Hour, Cast(PRECISETIME as DATETIME), Cast(DEVALERTas DATETIME)) > 4
Also note that it is a bad practice to store dates as varchar(). You should always avoid that.
还要注意，将日期存储为varchar()是不好的做法。你应该避免这种情况。
Try to set this:
试图设置:
set dateformat dmy
#2
2
You will need to CONVERT your columns into a DATETIME value and do a DATEDIFF() on them.
您需要将列转换为DATETIME值，并对它们执行DATEDIFF()。
You should really store your values as their appropriate datatypes, as this is not SARGable at all.
您应该将您的值存储为适当的数据类型，因为这一点都不受限制。
Select *
From GXHEAD
Where DateDiff(Hour, Convert(DateTime, PRECISETIME, 103), Convert(DateTime, DEVALERT, 103)) >= 4
#1
3
You can try this:
你可以试试这个:
DATEDIFF(Hour, Cast(PRECISETIME as DATETIME), Cast(DEVALERTas DATETIME)) > 4
Also note that it is a bad practice to store dates as varchar(). You should always avoid that.
还要注意，将日期存储为varchar()是不好的做法。你应该避免这种情况。
Try to set this:
试图设置:
set dateformat dmy
#2
2
You will need to CONVERT your columns into a DATETIME value and do a DATEDIFF() on them.
您需要将列转换为DATETIME值，并对它们执行DATEDIFF()。
You should really store your values as their appropriate datatypes, as this is not SARGable at all.
您应该将您的值存储为适当的数据类型，因为这一点都不受限制。
Select *
From GXHEAD
Where DateDiff(Hour, Convert(DateTime, PRECISETIME, 103), Convert(DateTime, DEVALERT, 103)) >= 4