SSIS variables: DateTime data type has no milliseconds

Everyone who has written more than few SQL queries is well aware of the issues that can occur when dealing with dates and times.  After a while you develop methods for getting around different date formats and eventually feel pretty confident that you are on top of the situation.  It’s always good to be aware that there are lots of places in the development world that can trip you up.

I discovered recently that the SSIS variable data type DateTime does not store milliseconds. 

To cut a long story short, when I ended up with some unexpected results it took a lot of head-scratching, testing and experimenting with changes and workarounds before sending out an SOS to colleagues who helped me find where I had gone wrong in order to get these results. But let me just say, it wasn't immediately obvious.

It brought home, once again, the danger in assuming things in this job.  Just because the data types are both from Microsoft (SQL Server and SQL Server Integration Services) and just because they have the same name (DateTime and datetime) they are not necessarily the same!

To get around this, I now store my SQL datetime data in SSIS String variables, converting them to a string by using the unambiguous 109 format: “mon dd yyyy hh:mi:ss:mmmAM (or PM)”.  My reasons for choosing this format were simply due to the “Default + milliseconds” standard it describes.  The choice is yours, just choose an unambiguous format. 

Whenever you need to use the value in a query, simply convert it back to datetime.