title: Match time, Concatenate to datetime, and store as datetime column in SQL Server 2012
body: <p>I ran into a weird issue today. Please help figuring out the <code>FirstTimeReported</code> column. <a href="http://sqlfiddle.com/#!3/2e9845/1" rel="nofollow">http://sqlfiddle.com/#!3/2e9845/1</a> </p>
  
  <p>Table columns:</p>
  
  <pre><code>SupportID int primary key, 
  DateEntered datetime, 
  ReportedTime varchar(4)
  </code></pre>
  
  <p>Input data</p>
  
  <pre><code>SupportID   DateEntered               ReportedTime
  1001        2015-04-13 09:15:00       0900
  2001        2015-04-14 00:10:00       2355
  </code></pre>
  
  <p>Expected results:</p>
  
  <pre><code>SupportID     FirstTimeReported
  1001          2015-04-13 09:00:00
  2001          2015-04-13 23:55:00
  </code></pre>
  
  <p>Update: Here is the Working solution 2 that I just modified from @Dan Guzman's code: </p>
  
  <pre><code>SELECT
        SupportID
      , CASE 
          WHEN DATEADD(minute, CAST(RIGHT(ReportedTime, 2) AS int), DATEADD(hour, CAST(LEFT(ReportedTime, 2) AS int), CAST(CAST(DateEntered AS date) AS datetime))) &lt;= DateEntered THEN DATEADD(minute, CAST(RIGHT(ReportedTime, 2) AS int), DATEADD(hour, CAST(LEFT(ReportedTime, 2) AS int), CAST(CAST(DateEntered AS date) AS datetime)))
          ELSE DATEADD(day, -1, DATEADD(minute, CAST(RIGHT(ReportedTime, 2) AS int), DATEADD(hour, CAST(LEFT(ReportedTime, 2) AS int), CAST(CAST(DateEntered AS date) AS datetime)))) 
        END AS FirstTimeReported
  FROM SupportContacts;
  </code></pre>