Unless you can guarantee that the person(s) or application will always provide a proper date and time, it's best to import the data into a staging table (as was done in the article) as VARCHAR and validate the data using standard techniques for validating such things. Once validated, simply inserting the data from the staging table to the final data table will do many implicit conversions such as converting proper VARCHAR renditions of date and time to an actual DATETIME (for example) data type of the target column in the final table.

As for "Upserts", it should be handled as any other data. You have a source table (the staging table) and a target table (the final table). I'll typically pre-mark each row with "I" (for insert) or "U" (for update) (sometimes, "D" for delete but I normally don't delete data due to audit requirements) using simple joins like you would in any classic upsert, and then do a separate insert and update. You could also use MERGE between the staging and target tables. BULK INSERT is not meant to provide upsert capabilities on its own. It's meant to be relatively simple and very fast... and it is.

If the source of the data is very good, you can, in fact, import into a staging table that has the correct data types. Even if the source is questionable in quality, you can setup BULK INSERT to sequester any bad lines of data in a separate file for future analysis and repair.