Importing date items to SharePoint list does not show the correct date

Question

I am using a PowerShell script to import list items from csv file to another SharePoint list

$SiteUrl = "http://mysite"
$ListName="Contacts"
$ImportFile ="c:\Scripts\EmpData.csv"

#Setup Credentials to connect
$Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($UserName,(ConvertTo-SecureString $Password -AsPlainText -Force))

#Set up the context
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteUrl)
$Context.Credentials = $credentials

#Get the List
$List = $Context.web.Lists.GetByTitle($ListName)

#Get the Data from CSV and Add to SharePoint List
$data = Import-Csv $ImportFile
Foreach ($row in $data) {

    #add item to List
    $ListItemInfo = New-Object Microsoft.SharePoint.Client.ListItemCreationInformation
    $Item = $List.AddItem($ListItemInfo)
    $Item["FirstName"] = $row.FirstName
    $Item["InfoDate"] = $row.InfoDate
    $Item.Update()
    $Context.ExecuteQuery()

}

For example, we assume the csv file contains a column called InfoDate with value 3/20/2018. After running the above script the InfoDate column in the destination list shows as 3/19/2018. I am not sure why this is happening and now to fix it.

Answer 1

It is happening because of "time zone" issue. You need to convert the time to UTC. Use LocalTimeToUTC method.l, this will insert the date filed in utc time zone.

Other than that, if u want to inset the date in a specific time zone, can follow the link bellow: https://blogs.msdn.microsoft.com/rslaten/2014/08/04/converting-times-from-one-time-zone-to-another-time-zone-in-powershell

Answer 2

If the time zone of the server is set differently than the Regional Setting in SharePoint, you will have this issue, especially if your source data is coming from the server. The default time of 00:00:00 is added to dates where time is not denoted. Therefore, your imported dates have a time of 00:00:00 with a time zone matching that of the server. Since your dates are 1 day off, it's likely that the server time is at least 1 hour ahead of your Regional Settings.

Check your Regional Settings in SharePoint:

• Site Settings > Site Administration > Regional Settings.

You do not want to change this because it will affect all other dates on the site. You can compare this with the server time to see the discrepancy.

If possible, get the date and time instead of just the date. Using the date and time in your import should accurately display the dates in SharePoint.

Answer 3

Had the same problem so in case anyone is looking for a solution here is what I did:

$UtcDate = Get-Date -Date $row.InfoDate
$Item["InfoDate"] = $UtcDate.ToUniversalTime()