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

Zack
Zack used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013
Commented:
cast the time to varchar too

select site + '-' + Cast (DisplayID as varchar(11)) + ' - Notification on' + Cast(ContactOnTime as varchar(8)) as CTime
from vwReg_Incidents_Posted
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ZackGeneral IT Goto Guy

Author

Commented:
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 VachhrajaniTechnical Architect, Capgemini India

Commented:
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 KumarDatabase Expert
Awarded 2016
Top Expert 2016

Commented:
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
ZackGeneral IT Goto Guy

Author

Commented:
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.
ste5anSenior Developer

Commented:
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 VachhrajaniTechnical Architect, Capgemini India

Commented:
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.
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
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.
ste5anSenior Developer

Commented:
ah, I would have chosen ContactTime for "contact on", time.
Database Expert
Awarded 2016
Top Expert 2016
Commented:
Pls try this-

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

Open in new window

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
Use CONVERT rather than CAST for a time, so that you can control the specific time format in the output. Most people understand AM/PM better than 24-hour, and getting that format requires CONVERT.  For example, if you wanted hh:mm:ssAM|PM format:

select site + '-' + Cast (DisplayID as varchar(11)) +
    ' - Notification on ' + replace(stuff(right(CONVERT(varchar(30), getdate(), 9), 14), 9, 4, ''), ' ', '0') as CTime
from vwReg_Incidents_Posted

if you want just hh:mmAM|PM:

    ' - Notification on ' + replace(stuff(right(CONVERT(varchar(30), getdate(), 9), 14), 6, 7, ''), ' ', '0') as CTime

Naturally CONVERT could output 24-hour instead if you need it, so it's more flexible than CAST for time.
ZackGeneral IT Goto Guy

Author

Commented:
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 :)
ZackGeneral IT Goto Guy

Author

Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial