I'm currently hitting a limitation on Azure SQL DW when converting timestamps from the JVM SimpleDateFormat domain
to SQL DW's DateFormat domain.
In my current scenario, we're using Spark to perform data cleanup, when data is written out as a CSV file, its written out as a timestamp in the following format:
It looks like unless you transform the datetime itself before it hits SQL DW, it won't be interpreted as datetime due to the T. Your best bet is having it as varchar and then casting it as a datetime in SQL DW as listed here: https://msdn.microsoft.com/en-us/library/ms187928.aspx#Examples: Azure SQL Data Warehouse and Parallel Data Warehouse
I would recommend also casting a vote in the feature request portal for more flexible datetime formats so you don't have to go through this process