tags:

views:

121

answers:

4

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);
+2  A: 

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.

Thorsten Dittmar
but i alredy do that but still has the same exception
Edited my answer.
Thorsten Dittmar
@sama: Please do not edit the original post to reflect changes suggested in answers. This renders the answer invalid. The first parts of my and TheVillageIdiot's answers will now seem incorrect, as the single quotes that were there when we answered are gone now.
Thorsten Dittmar
i do that but it throw the"Conversion failed when converting datetime from character string." exception
Post table definition please.
Thorsten Dittmar
this is the table definitiontagID bigint enterTime nchar(10) exitTime nchar(10) date nchar(10)
enterTime is not of type DATETIME, so it can't be used in the DATEDIFF function. What's the point in storing a DateTime in a nchar(10) column anyways?
Thorsten Dittmar
The question, by the way, should not be tagged "C#", but "SQL", as your problem clearly is not in the C# code but in your SQL Table definition (in my opinion) or in the query string, in case you want to keep the column data type.
Thorsten Dittmar
but i can't change the types of entertime and exittime in the tableit say can't be changed
ok i will try to make anew table
see my above edit 3.
Thorsten Dittmar
A: 

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.

Paddy
+1  A: 

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
TheVillageIdiot
A: 

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.

James