Apr 242012
 

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]

  4 Responses to “Populating Date fields in SharePoint using SSIS”

  1. or you could use:

    select convert(char(10), getdate(), 121) as [Adjustment Date]

  2. 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.

  3. 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.

 Leave a Reply

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

(required)

(required)

Wordpress SEO Plugin by SEOPressor