Given a single row that contains StartDate and EndDate columns as DATETIME2 columns, write the code to calculate the duration between those two columns and display it in the HHHHHHHH:MI:SS.mil format.
If I had something as a datetime2 I would never need the DATEDIFF in this format. I assumed you meant something about the number returned was incorrect. If I need time, I wouldn't use datetime2.
But do you ever store both the short Date and Time in separate columns in the same table?
In a certain sense, sometimes. In my data warehouse, the items for which time is also important have two columns for the item, one date and the other time. Otherwise the dimension storing the date and time values would explode in number of rows.