exception handling wanted to be solved please

Question

this is my statement that throw exception says "can;t convert from string to smalldatetime value " pleaze help me to fix it or write the correct statement

da = new SqlDataAdapter("SELECT name,[build-id],exitTime,enterTime,tagType FROM Employees,GateLogging WHERE GateLogging.tagType='Employee' AND enterTime=DATEDIFF(minute,GateLogging.enterTime,GETDATE())>10", MyConn);

Answer 1

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.

Answer 2

I'd also not for you that you're not using ANSI style join syntax - this might cause you a few problems in later life.

Answer 3

First thing you were putting DATEDIFF stuff in quotes (') and comparing it with enterTime which I suspect is smalldatetime type so you got the error. Correct SQL will be:

  SELECT name,[build-id],exitTime,enterTime,tagType 
  FROM Employees,
  GateLogging 
  WHERE 
      GateLogging.tagType='Employee'
  AND
      enterTime = DATEDIFF(minute,GateLogging.enterTime,GETDATE())

As for your >10 I think you should not compare it with enterTime but use this instead:

    SELECT name,[build-id],exitTime,enterTime,tagType
    FROM Employees,
    GateLogging 
    WHERE 
    GateLogging.tagType='Employee'
    AND
    DATEDIFF(minute,GateLogging.enterTime,GETDATE()) >10

Answer 4

I am pretty sure this is just a simple formatting issue with the string you are passing into the query.

Make sure your date string is in a valid date format and try again.