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.
ZackGeneral IT Goto GuyAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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

0
 
PortletPaulConnect With a Mentor freelancerCommented:
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
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Pawan KumarDatabase ExpertCommented:
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
0
 
ZackGeneral IT Goto GuyAuthor 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.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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

0
 
Pawan KumarDatabase ExpertCommented:
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
0
 
ZackGeneral IT Goto GuyAuthor 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.
0
 
ste5anSenior DeveloperCommented:
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.
0
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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.
0
 
PortletPaulfreelancerCommented:
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.
1
 
ste5anSenior DeveloperCommented:
ah, I would have chosen ContactTime for "contact on", time.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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.
0
 
ZackGeneral IT Goto GuyAuthor 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 :)
0
 
ZackGeneral IT Goto GuyAuthor 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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.