title: Combine separate date and time columns into one column
body: <p>I have a situation for which I need some suggestions. No code as I want to look that up and figure it out myself. This is just an academic question.</p>
  
  <p>I get a flat file daily that I have to load into a table here is a snippet of the file:</p>
  
  <pre><code>Date-YYYMMDD  TIME
  20091124|     01:19:00|...
  20091124|     01:19:15|...
  </code></pre>
  
  <p>The two fields are date and time. when I load this data into the database, the results are these:</p>
  
  <pre><code>Date-YYYMMDD  TIME
  20091124      1899-12-30 01:19:00.000
  20091124      1899-12-30 01:19:15.000
  </code></pre>
  
  <p>I have a view that merges the data with the results being:</p>
  
  <pre><code>datetime                   yearmonthday
  2009-11-24 01:19:00.000    2009-11-24
  2009-11-24 01:19:15.000    2009-11-24
  </code></pre>
  
  <p>I do this effort so that the folks that need this data don't have to fool around with dates and text fields that should be dates but are not.</p>
  
  <p>The problem is I don't like doing this in a view. I would rather just store it in the original table correctly. I was thinking that for every row inserted, I intercept that insert, do the merging then insert into the table in the correct format. Once done, I could drop the view. Is there a best way for this? Like any good developer/admin. I want to minimize my intervention. We have computers to do this type of work. I do not control the source file nor can I effect any changes with it. I was thinking an <strong>on insert</strong> trigger. Is there a better way? Though I have done a lot with TSQL over the months, I still consider myself a beginner in my understanding of good TSQL and the most efficient way to do things.</p>