title: Increment date based on the time value automatically in SQL
body: <p>I have a client data which has the candidate id, a start date which will have only the date in varchar format and a time value associated to that date in varchar format in a seperate column.</p>
  
  <p>To give a brief explanation about the data a candidate will come to study center at any time point in a day for example 10:00 AM on 20-10-2014. The start date will be 20-10-2014 and from 10:00 AM he will have test based on the time point. So if the time point is 2 HR, then at 12:00 PM he will have a test. If the time point is 8 HR, it will get added with 10:00 AM and based on this he will have the test at 06:00 PM. When the timepoint reaches 00:00 the start date needs to be the next date instead of 20-10-2014. </p>
  
  <p>The start date needs to be appended with the time value such that when it crosses over the time 00:00, the start date needs to get increased by 1 ie the next day. I have added the start date to the time by using the code below </p>
  
  <p>CAST(STARTDATE as datetime) + CAST(CAST(STUFF([TIME],3,0,':') as time(0)) as datetime) as [EXPECTEDDATETIME]</p>
  
  <p>By this above code i have created the expected datetime however </p>
  
  <p>I will not be able to hardcode the value due to dynamic data. I tried to apply >= and &lt; to the time value something like </p>
  
  <pre><code>case when MyTime &gt;= '00:00' and MyTime &lt; '08:10' the Dateadd(day, 1, date)
  </code></pre>
  
  <p>This works perfect but my concern is that I cannot put the value 08:10 because it will not a constant value for all rows.</p>
  
  <p>I have provided a screenshot of my data and a expected date column for reference.</p>
  
  <pre><code>Candidate   StartDate   Time    Expected DateTime       Timepoint
  1           20141020    1000    2014-10-20 10:00:00     0 HR
  1           20141020    1200    2014-10-20 12:00:00     02 HR
  1           20141020    1400    2014-10-20 14:00:00     04 HR
  1           20141020    1800    2014-10-20 18:00:00     08 HR
  1           20141020    0000    2014-10-21 00:00:00     12 HR
  1           20141020    1200    2014-10-21 12:00:00     24 HR
  1           20141020    1300    2014-10-21 13:00:00     25 HR
  2           20141020    1100    2014-10-20 11:00:00     0 HR
  2           20141020    1300    2014-10-20 13:00:00     02 HR
  2           20141020    1500    2014-10-20 15:00:00     04 HR
  2           20141020    1900    2014-10-20 19:00:00     08 HR
  2           20141020    2100    2014-10-20 21:00:00     12 HR
  2           20141020    2300    2014-10-20 23:00:00     24 HR
  2           20141020    0230    2014-10-21 02:30:00     27 HR
  2           20141020    1330    ..............
  3           20141026    1530    ...............
  3           20141026    2000    
  3           20141026    0930    
  3           20141026    1020    
  3           20141026    1120    
  </code></pre>
  
  <p>Can anyone help me in this request? </p>