Adding a varchar to a datetime column and dealing with nulls I have two columns in my table a varchar(8) titled Trans_Time which holds data in an XX:XX:XX time format and a DateTime column named Trand_Date which holds just a the date component in XX-XX-XXXX format. 

I am trying to combine the two columns to get a full date and time (i.e. if Trans_Time is 08:10:14 and Trand_Date is 04-04-2007 the output should be 04-04-2007 08:10:14). 

My problem is that some times the Trans_Time is null and this produces an output of null. 

Is there some way I can treat null as 00:00:00?
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