"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?
 
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
 
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:
The date in SQL is for storage only for another application. So the format stored in SQL isn't a huge issue.
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.