How to Combine dates and time in separate fields in SQL

Table :

schedule_id job_id      next_run_date   next_run_time
------------------------------------------------------
221         D23EA7B2    20151005            90000
222         18EDFB21    20151020            90000
242         90283725    20151001            170000
239         4B69C670    20151011            90000

Result :

schedule_id  job_id      next_run_date_Time 
--------------------------------------------
221         D23EA7B2    2015-10-05 09:00 AM
222         18EDFB21    2015-10-20 09:00 AM
242         90283725    2015-10-01 05:00 PM
239         4B69C670    2015-10-11 09:00 AM

How to join next_run_date and next_run_time together as a single column?

My Query used in SSRS 2008

    SELECT c.Name AS ReportName,[LastRunTime],
'Next Run Date' = CASE next_run_date WHEN 0 THEN null ELSE
substring(convert(varchar(15),next_run_date),1,4) + '/' +
substring(convert(varchar(15),next_run_date),5,2) + '/' +
substring(convert(varchar(15),next_run_date),7,2)
END,
--Need to add next_run_date_Time here
FROM 
dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id
ORDER BY S.LastRunTime DESC

Solution

Assuming both are varchar, try this:

SELECT schedule_id, job_id,
       CONVERT(datetime, next_run_date, 112)
       + CONVERT(time,
                 SUBSTRING(next_run_time, 1, LEN(next_run_time) - 4) + ':'
                 + LEFT(RIGHT(next_run_time, 4), 2) + ':'
                 + RIGHT(next_run_time, 2),
               114) AS next_run_date_Time
FROM my_table

Here is a fiddle

If those fields are numbers, you can convert them in a sub-query first and then apply the same query above:

SELECT schedule_id, job_id,
       CONVERT(datetime, next_run_date, 112)
       + CONVERT(time,
                 SUBSTRING(next_run_time, 1, LEN(next_run_time) - 4) + ':'
                 + LEFT(RIGHT(next_run_time, 4), 2) + ':'
                 + RIGHT(next_run_time, 2),
               114) AS next_run_date_Time
FROM (SELECT schedule_id, job_id
           , CAST(next_run_date AS VARCHAR(8)) AS next_run_date
           , CAST(next_run_time AS VARCHAR(6)) AS next_run_time
      FROM my_table) AS t

Here is a fiddle

EDIT you can update your query to use this concept like this:

SELECT c.Name AS ReportName,[LastRunTime],
       CONVERT(datetime, next_run_date, 112)
       + CONVERT(time,
                 SUBSTRING(next_run_time, 1, LEN(next_run_time) - 4) + ':'
                 + LEFT(RIGHT(next_run_time, 4), 2) + ':'
                 + RIGHT(next_run_time, 2),
               114) AS 'Next Run Date'
FROM 
dbo.[Catalog] c
INNER JOIN dbo.[Subscriptions] S ON c.ItemID = S.Report_OID
INNER JOIN dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID
INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name
INNER JOIN (SELECT schedule_id, job_id
                 , CAST(next_run_date AS VARCHAR(8)) AS next_run_date
                 , CAST(next_run_time AS VARCHAR(6)) AS next_run_time
            FROM msdb.dbo.sysjobschedules) AS JS ON J.job_id = JS.job_id
ORDER BY S.LastRunTime DESC