Database

declare @t table(START_DATE varchar(10), END_TIME_1 varchar(8), START_TIME_1 varchar(8), END_DATE varchar (10))
insert @t values('', '20:12:52', '', '01/18/2015')

select Datediff(SECOND,
             convert(datetime, [START_DATE] + ' ' + [START_TIME_1]),
             convert(datetime, [END_DATE] + ' ' + [END_TIME_1]))
from @t

declare @t table(START_DATE DateTime, END_DATE DateTime)
insert @t values ('01-jan-2015 20:12:52', Null)

select Datediff(SECOND,[START_DATE], ISNULL([END_DATE],[START_DATE]))
from @t

, CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0))                              AS [START_DATE]

, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.START_TIME,1,8)), 6, 0)AS nvarchar(6))   AS START_TIME

, CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0))                                  AS END_DATE

, CAST(dbo.LPAD(dbo.CONVERT_TIME_TO_NUMERIC(SUBSTRING(dbo.arKD.END_TIME,1,8)), 6, 0)AS nvarchar(6))     AS END_TIME

--DATEDIFF(interval, Starting_date datetime, ending_date datetime)
,Datediff(SECOND,
     convert(datetime, [START_DATE] + ' ' + [START_TIME]),
     convert(datetime, [END_DATE] + ' ' + [END_TIME]))                                                  AS M_Time_Worked

,Datediff(SECOND,
     convert(datetime, [START_DATE] + ' ' + [START_TIME]),
     convert(datetime, CASE WHEN RTRIM([END_DATE]) = '' THEN [START_DATE] ELSE [END_DATE] END + ' ' + CASE WHEN RTRIM([END_TIME]) = '' THEN [START_TIME] ELSE [END_TIME] END))                                                  AS M_Time_Worked

The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart.

Ambertje wrote:

I need my dates as string because I'm working with a Data warehouse DB.

,Datediff(SECOND,
     convert(datetime, [START_DATE] + ' ' + [START_TIME]),
     convert(datetime, [END_DATE] + ' ' +   END_TIME]))                                                  AS M_Time_Worked

Ambertje wrote:

So I think ISNULL(Start_Date, Start_Time) is what I need.

ISNULL(Start_Date, EndDate) + ' ' + ISNULL(Start_Time, Endtime)

,Datediff(SECOND,
     convert(datetime, ISNULL( NULLIF([START_DATE], '' ) , END_DATE) + ' ' + ISNULL( NULLIF([START_TIME], '' ) , END_TIME)),
     convert(datetime, [END_DATE] + ' ' + [END_TIME]))                                                  AS M_Time_Worked

, CASE WHEN [START_DATE] IS NULL OR [START_DATE] = '' THEN
    CAST(Replace(convert(date, END_DATE, 101), '-', '') as numeric(8,0))    ELSE
        CAST(Replace(convert(date, [START_DATE], 101), '-', '') as numeric(8,0)) END                    AS [START_DATE]