Adding a varchar to a datetime column and dealing with nulls

Question

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?

Answer 1

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 < 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()) < 1000

Answer 2

Try using isnull to replace the null value.

select isnull(null, '00:00:00')