asked on

"The data types nvarchar and time are incompatible in the add operator" How to resolve?

I have a database with [Date] and [Time] columns, both of which used to be in nvarchar(50) format. I also have an SQL statement that shows data that merges the [Date] and [Time] columns into [Date + Time] column:

select A.[Inv], A.[Project], [Date + Time], A.[Description], A.[Details], A.[Status],
 A.[Accumulative], A.[Preventive], A.[PIC],  
 convert(float, A.[Accumulative]), convert(float, A.[Preventive]), A.[PIC] 
 from [SQLIOT].[dbo].[ENTRY] A 
 left join (select [Inv], max(Date +' '+ Time) as [Date + Time] 
 from [SQLIOT].[dbo].[ENTRY] 
 group by [Inv]) B on A.[Inv] = B.[Inv] 
 where [Date + Time] = Date +' '+ Time  
 Order by A.[Status], A.[Date]desc

Open in new window

Now I want to change the format for both columns into [Date] = date & [Time] = time(0). Because right now it cannot give me the correct info since time is string.

Example: 2 rows with time 9:00 am and 11:00 am, 9:00 am will be displayed because it is read as 9 instead of 09.

But now when I try the SQL I get the message:

The data types nvarchar and time are incompatible in the add operator

Open in new window

What should I add to the SQL to help merge both columns into datetime?

Pavel Celba

Start with the table structure redesign.

Don't use the [Date] and [Time] columns as nvarchar(50). Design just ONE column of DateTime data type and convert it to whatever output format is needed in your app (or in SQL query when no other way exists).

You could also disclose what database engine is used.

ste5an

hmm, I have a déjà vu right now.. I'm pretty sure we did you tell that all ready...

Or when the model requires it use DATE and TIME.

but you can simply use CAST() or TRY_CAST(), when we talk about SQL Server here:

DECLARE @DateTime DATETIME = GETDATE();
DECLARE @Date DATE = @DateTime;
DECLARE @Time TIME = @DateTime;

SELECT @DateTime ,
       CAST(@Date AS DATETIME) + CAST(@Time AS DATETIME);

Open in new window

Just to repeat myself: Sticking to NVARCHAR() can give you incorrect results by using MAX() depending on the used date format.

btw, using reserver words as object names is a bad habit.

E.g. this works with valid formatted dates in your nvarchar columns:

WITH Daten
AS ( SELECT A.Inv ,
            A.Project ,
            CAST(A.[Date] AS DATETIME) + CAST(A.[Time] AS DATETIME) AS [DateTime] ,
            A.[Description] ,
            A.Details ,
            A.[Status] ,
            A.Accumulative ,
            A.Preventive ,
            A.PIC ,
            A.Accumulative ,
            A.Preventive ,
            A.PIC
     FROM   SQLIOT.dbo.[ENTRY] A )
SELECT   A.Inv ,
         A.Project ,
         B.MaxDateTime ,
         A.[Description] ,
         A.Details ,
         A.[Status] ,
         A.Accumulative ,
         A.Preventive ,
         A.PIC ,
         CONVERT(FLOAT, A.Accumulative) ,
         CONVERT(FLOAT, A.Preventive) ,
         A.PIC
FROM     Daten A
         LEFT JOIN (   SELECT   I.Inv ,
                                MAX(I.[DateTime]) AS MaxDateTime
                       FROM     Daten I
                       GROUP BY I.Inv ) B ON A.Inv = B.Inv
WHERE    B.MaxDateTime = A.[DateTime]
ORDER BY A.[Status] ,
         A.Date DESC;

Open in new window

ASKER CERTIFIED SOLUTION

Hans J.Hau

membership

This solution is only available to members.

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

Start Free Trial

Hans J.Hau

ASKER

The date in SQL is for storage only for another application. So the format stored in SQL isn't a huge issue.

&quot;The data types nvarchar and time are incompatible in the add operator&quot; How to resolve?

"The data types nvarchar and time are incompatible in the add operator" How to resolve?