2

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?

3

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
0

Try using isnull to replace the null value.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.