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]
or you could use:
select convert(char(10), getdate(), 121) as [Adjustment Date]
Thanks for that comment, Quentin. I wasn’t aware I could take a portion of the date format just by limiting the number of characters. Just to clarify, SharePoint if the date is a one digit day or month, SharePoint needs it in the format ‘yyyy-m-d’. So my SQL statement still applies, unless you have another tip for me.
I had to add an outer CAST( … as varchar(10)) to the above syntax in order to have SSIS recognize it as a string and not a date.