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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
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
Qlemo"Batchelor", 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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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
PortletPaulEE Topic AdvisorCommented:
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
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.