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]
group by [Inv]) B on A.[Inv] = B.[Inv]
where [Date + Time] = Date +' '+ Time
Order by A.[Status], A.[Date]desc
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
What should I add to the SQL to help merge both columns into datetime?