In the WHERE
clause, remove the single quotes around the DATEDIFF(minute,GateLogging.enterTime,GETDATE())>10
statement.
EDIT
You're also comparing a datetime field against what I'd call a boolean. Remove the enterTime=
. Your statement should look like this:
da = new SqlDataAdapter("SELECT name,[build-id],exitTime,enterTime,tagType FROM Employees,GateLogging WHERE GateLogging.tagType='Employee' AND DATEDIFF(minute,GateLogging.enterTime,GETDATE())>10", MyConn);
EDIT 2
Your table definition is as follows:
tagID bigint
enterTime nchar(10)
exitTime nchar(10)
date nchar(10)
Of course, enterTime
can not be used in DATEDIFF
, as it is not a DATETIME
.
Question: Why are you storing dates and times as NCHAR(10) instead of DATETIME
? That's not good style!
Solution 1: Change enterTime
and exitTime
to DATETIME
and you're fine.
Solution 2: Change your statement, so that you convert enterTime
to a valid DATETIME
. I assume that enterTime
only contains the time of day, so you'd have to mix in the date part before converting.
EDIT 3
Assuming that date
stores the day in format yyyymmdd
and enterTime
stores the time in format hh:mm:ss
, you'll be able assemble a DATETIME
:
CONVERT(DATETIME, SUBSTRING(date, 1, 4) + '-' + SUBSTRING(date, 5, 2) + '-' + SUBSTRING(date, 7,2) + ' ' + entryTime, 102)
So your statement from above would look like:
da = new SqlDataAdapter(
"SELECT name,[build-id],exitTime,enterTime,tagType
FROM Employees,GateLogging
WHERE GateLogging.tagType='Employee' AND
DATEDIFF(minute,CONVERT(DATETIME, SUBSTRING(date, 1, 4) + '-' + SUBSTRING(date, 5, 2) + '-' + SUBSTRING(date, 7,2) + ' ' + entryTime, 102),GETDATE())>10", MyConn);
In case the date/time format stored in the fields of your database are different, you'll have to adjust the SUBSTRING
statements within the CONVERT()
accordingly.