Adding a varchar to a datetime column and dealing with nulls
You can use ISNULL or COALESCE in T-SQL to replace a null value:

SELECT 
  Trand_Date + ' ' + COALESCE(Trans_Time, '00:00:00') AS Trand_DateTime1,
  Trand_Date + ' ' + ISNULL(Trans_Time, '00:00:00') AS Trand_DateTime2
FROM MyTable


Both columns above should return the same value. Note that if you want to perform date-based comparisons, e.g. DATEDIFF, DATEADD or even something as simple as WHERE Trand_DateTime &lt; GETDATE(), you can create a view of the table that presents the values as an actual SQL DateTime datatype like so:

CREATE VIEW [dbo].[ViewTransDateTimes]
AS
SELECT 
  CAST(Trand_Date + 'T' + COALESCE(Trans_Time, '00:00:00') as DateTime) AS Trand_DateTime
  --, [OTHER COLUMNS]
FROM MyTable
GO


Then you can do things like:

SELECT * FROM ViewTransDateTimes
WHERE DATEDIFF(day, Trand_DateTime, GETDATE()) &lt; 1000