CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user2496503
user2496503

Reputation: 927

Operand data type varchar is invalid for subtract operator

I need to Subtract the datetime type data for example from a column like:

         datetime
    20/03/2013:03:17:43
    20/03/2013:03:17:43
    20/03/2013:03:17:44
    20/03/2013:03:17:44
    20/03/2013:03:17:44
    20/03/2013:03:17:44

I am using SQL Server R2 2008; with my Query like:

    SELECT basescore,

    MAX(datetime) - MIN(datetime)

    FROM log

    GROUP BY basescore

But all the time it gives me an error:

       Msg 8117, Level 16, State 1, Line 3
       Operand data type varchar is invalid for subtract operator.

Can somebody help me solving this error? Thanks in advance!

Upvotes: 2

Views: 32843

Answers (4)

openritwanz
openritwanz

Reputation: 49

"Operand data type varchar is invalid for subtract operator" the data type varchar is invalid .. so use CAST to convert data type as datetime

Upvotes: 2

Yuriy Galanter
Yuriy Galanter

Reputation: 39777

You column is of a VarChar type. Please use conversion in conjunction with DATEDIFF function.

SELECT basescore, DATEDIFF(DD,MAX(CAST([datetime] as datetime)), MIN(CAST([datetime] as datetime))) FROM log GROUP BY basescore

But I agree - if the column contains datetime data - it should be of a datetime type.

UPDATE: You have a semicolon between date and time. If you do not have control over imported data you can modify the above query as

SELECT basescore, DATEDIFF(DD,MAX(CAST(STUFF([datetime],11,1,' ') as datetime)), MIN(CAST(STUFF([datetime],11,1,' ') as datetime))) FROM log GROUP BY basescore

But as you see it's getting more and more involved. It would be much easier and safer if the column were of datetime type.

Upvotes: 3

Sonam
Sonam

Reputation: 3466

 SELECT basescore,DATEDIFF(DD,MAX(datetime),MIN(datetime))FROM log GROUP BY basescore

Upvotes: 0

CaveCoder
CaveCoder

Reputation: 791

you have to use datediff function

http://msdn.microsoft.com/en-us/library/aa258269(v=sql.80).aspx

Thanks

Upvotes: -3

Related Questions