CODEWITHSUNDEEP
sql-server
shinken
shinken

Reputation: 23

How to correct an error in SELECT statement

I've coded on SQL Server 2014:

create table CriticalStop
(
    SAP_ID int not null,
    Affected_ID char(15) not null,
    FnLoc_ID char(20) not null,
    MalFn_date date,
    MalFn_time time,
    MalFnHour_dur float,
    MalFnMajor char(15),

    primary key(SAP_ID)
);
go

insert into CriticalStop(SAP_ID, Affected_ID, FnLoc_ID, MalFn_date, MalFn_time, MalFnHour_dur, MalFnMajor)
values (10045929, 'TN.211-HC1', 'TN.211-HC1', convert(date,'20190101'), convert(time,'18:29:00.290'), 3.52, 'Process')

They was okay. but when I used statement below, SQL Server 2014 raised an error

select 
    SAP_ID,
    Affected_ID,
    FnLoc_ID,
    MalFn_date+3 as MalFn_delay,
    MalFn_time,
    MalFnHour_dur,
    MalFnMajor
from 
    CriticalStop;

Error:

Msg 206, Level 16, State 2, Line 2314
Operand type clash: date is incompatible with int.

Line 2314 was pointing to SELECT statement above. I don't understand why this error happens on line MalFn_date+3 as MalFn_delay. I remove +3, the result is okay, but I need to change its date. How can I get it working with +3.

Upvotes: 0

Views: 47

Answers (1)

Soviut
Soviut

Reputation: 91535

It says what the problem is right in the error message, date is incompatible with int. You're trying to add an integer to a date field.

MalFn_date+3 as MalFn_delay,

You need to convert the integer to a time range so that it can be added to the date. SQL has no idea what 3 represents; seconds, minutes, days?

You can use the DATEADD() function to do this in SQL Server. For example, if you wanted to add 3 days, 

DATEADD(day, 3, MalFn_date)

Upvotes: 4

Related Questions