asked on

SQL - Data types varchar and time are incompatible in the add operator.

Hi EE

I have the following query.

select site + '-' + Cast (DisplayID as varchar(11)) + ' - Notification on' + Cast(ContactOnTime as time) as CTime
from vwReg_Incidents_Posted

I get the following error.

Msg 402, Level 16, State 1, Line 1 The data types varchar and time are incompatible in the add operator.

Is their way around this issue or this a limitation of SQL?

Any assistance is appreciated.

Thank you.

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Qlemo

You'll have to convert the time into varchar, that is all. See convert function. The currently used cast might be still required or not, depending on what you have as original datatype.

Pawan Kumar

Use concat. No need for conversion

select concat(site ,'-' ,Cast (DisplayID as varchar(11)) ,' - Notification on' ,Cast(ContactOnTime as time)) as CTime
from vwReg_Incidents_Posted

Zack

ASKER

Hi Pawan,

The concat statement isn't working for me:

Msg 195, Level 15, State 10, Line 1
'concat' is not a recognized built-in function name.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'and'.

Using SQL server 2008

Thank you.

Nakul Vachhrajani

Here you go. You need to CAST the TIME to a VARCHAR. I have done that in my code below (tested in SQL 2008 R2):

DECLARE @site VARCHAR(20) = 'UseActualColumnNames'
DECLARE @displayId INT = 1111;
DECLARE @ContactOnTime TIME = GETDATE();

SELECT @site + '-' + CAST(@displayId AS VARCHAR(11)) + ' - Notification on: ' + CAST(CAST(@ContactOnTime AS TIME) AS VARCHAR(20)) AS CTime

/* RESULTS
-----------
CTime
------------------------------------------------------------------------
UseActualColumnNames-1111 - Notification on: 14:07:54.4430000
*/

Open in new window

Pawan Kumar

Yes it will work for sql 2012+

U can use below for 2008
Cast all fields other than varchar.

select cast(site as varchar (100))+ '-' + Cast (DisplayID as varchar(11)) + ' - cast(Notification on as varchar(30))' + cast(Cast(ContactOnTime as time) as varchar(30)) as CTime
from vwReg_Incidents_Posted

Zack

ASKER

Hi Pawan,

Not sure what you mean I am getting the following result when running the query:

BWH-334610 - cast(Notification on as varchar(30))15:00:00.0000000

Thank you.

ste5an

Just a comment:

Column and table names form the relational model and should be meaningful and form correct semantics.

ContactOnTime sounds to me like a BIT column. Contact is either on time or not.
When it is earlier or later or never, then it is a coded value with a check constraint or DKNF.

Nakul Vachhrajani

Did you try the query outlined in ID: 42382489? I have used local variables, but you can easily adapt it for use in your script.

PortletPaul

Ste5an:

Contact "on time": would be yes/no
Contact On, time: would be an event at some date/time

Yet another regrettable foible of the English language I suspect.

ste5an

ah, I would have chosen ContactTime for "contact on", time.

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zack

ASKER

Hi Portletpaul,

Contact "on time": would be yes/no
Contact On, time: would be an event at some date/time

Yet another regrettable foible of the English language I suspect.

I agree with your grammatical analysis :)

Zack

ASKER

Cheers for the help guys my internet connection went down hence my slow response.
I used the solution from Pawan, but thank you, Scott for informing about the 'replace' and 'stuff' commands it will come handy in the future.