Writing a date + time inside a SP list with the Destination Adapter

Mar 4, 2010 at 9:08 AM

I'm using the SharePoint List adapter destination to write in a SP list from a SQL table. I have a datetime column with value expressed with date + hour + minutes.

I have two servers, the web server and the db server. On the db server I have installed the Adapters (SQL Server 2005 RTM): the Regional Settings is set to en-us

and the hour is set to AM/PM (but I think it is no matter). The web server that has the SharePoint content db are the same Regional settings (en-us + hour in AM/PM).

I'm using the same regional settings as site settings.

For a SQL datetime as 2010-03-19 10:00 PM, the adapter writes 2010-03-19 10:00 AM.

For writing in my list, in SSIS I must convert my SQL datetime in a DT_DATE: I have used a grid data viewer to observe the data passed before and after

the data conversion and before the writing and I can see the same datetime in the SQL table.

Any suggests to solve this issue, please? Many thanks

Mar 4, 2010 at 11:36 AM

I have tried to modify the time format of the Regional settings on the db server and the web server by changing it as H:mm:ss,

with nothing results.

Any helps is necessary to solve this issue, please. Many thanks

Mar 4, 2010 at 12:22 PM

Nothing results also if I set the time zone to Greenwich one.

Any helps, please. Thanks

Mar 4, 2010 at 1:36 PM

Do I convert my SQL date in a particular date format? Thanks

Mar 4, 2010 at 9:24 PM
Edited Mar 5, 2010 at 7:07 AM

In the project code (SharePointListDestination.cs) I can see these statements:

case DataType.DT_DBTIMESTAMP:
                                if (buffer.IsNull(_bufferLookup[fieldName]))
                                    rowData.Add(fieldName, String.Empty);
                                else
                                    rowData.Add(fieldName, buffer.GetDateTime(_bufferLookup[fieldName]).ToString("yyy-MM-dd hh:mm:ss"));
                                break;

Is it rigth to write yyy (3 chars for the year)?

Is it right to write hh:mm:ss? In the regional settings, hh indicates hour expressed with AM/PM but I cannot see "tt" after "ss";

moreovert, to render the hous as 24 hours format it is to use HH (uppercase) and not hh.

Perhaps it occurs to substitute "yyy-MM-dd hh:mm:ss" with "yyyy-MM-dd hh:mm:ss tt" or "yyyy-MM-dd HH:mm:ss".

To write correctly into a SP list, do I convert my SQL date in a particular date format, please? Thanks