"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?
Hans J.HauAsked:
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.

pcelbaCommented:
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.
0
ste5anSenior DeveloperCommented:
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

0
Hans J.HauAuthor Commented:
Accidentally found the answer. All I needed to do was set both columns into datetime format. Now I can get them to merge during display.
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
Hans J.HauAuthor Commented:
The date in SQL is for storage only for another application. So the format stored in SQL isn't a huge issue.
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
Databases

From novice to tech pro — start learning today.