Populating Date fields in SharePoint using SSIS

This is an addendum to my previous blog post How to use a SharePoint list as a data source in your SSIS package.

If you are populating a SharePoint lists with a field of date type, you need to format the date as text, in the following format, in order for SharePoint to accept the input from SSIS.

‘yyyy-mm-dd’      or for one digit months or days      ‘yyyy-m-d’

So if you’re using a SQL Query you can write something really ugly, like this:

SELECT �
CAST(DATEPART(Year, GETDATE()) AS varchar(4)) + ‘-‘ + CAST(DATEPART(Month, GETDATE()) AS varchar(2)) +  ‘-‘ + CAST(DATEPART(Day, GETDATE()) AS varchar(2))  AS [Adjusment Date]