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.
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.
Microsoft SQL ServerSQL
Last Comment
Zack
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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
select concat(site ,'-' ,Cast (DisplayID as varchar(11)) ,' - Notification on' ,Cast(ContactOnTime as time)) as CTime
from vwReg_Incidents_Posted
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.
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.
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
*/
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
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
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.00000
Thank you.
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.00000
Thank you.
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.
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.
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.
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.
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.
ah, I would have chosen ContactTime for "contact on", time.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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 :)
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 :)
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.
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.