Danny Hansen

In a project that I am working on we used FullTextSql query’s. After a while we noticed that dates coming from FullTextSql where a day before actual date. We use Dutch culture so this looked like the obvious reason. After investigating sulution was simple. Sharepoint stores all its dates in UTC time.

So when I add a date field date = 12-12-2011 : 00:00:00, Sharepoint stores this as 11:12:2011 : 23:00:00

Lets take a look at the method below. When we display the DateFieldValue field the date is not showing correct on my Dutch site.

		public List GetResultItems()
		{
			ResultTableCollection queryResults;

			try
			{
				queryResults = new FullTextSqlQuery(SPContext.Current.Site)
				{
					QueryText = @"SELECT ID, Title, Url, DateFieldValue",
					ResultTypes = ResultType.RelevantResults,
					TrimDuplicates = false
				}.Execute();
			}
			catch (Exception ex)
			{
				return new List();
			}

			var queryResultsTable = queryResults[ResultType.RelevantResults];
			var ResultItems = new DataTable();
			ResultItems.Load(queryResultsTable, LoadOption.OverwriteChanges);
			var listOfResultItems = new List();

			try
			{
				// assembly list of Result from the FullTextSqlQuery
				listOfResultItems = (from DataRow row in ResultItems.Rows
										  select new ResultItem
										  {
											  Id = !(row["ID"] is DBNull) ? int.Parse(row["ID"].ToString()) : 0,
											  Title = !(row["Title"] is DBNull) ? (string)row["Title"] : "",
											  Url = !(row["Url"] is DBNull) ? (string)row["Url"] : "",
											  DateFieldValue = !(row["DateFieldValue"] is DBNull) && row["DateFieldValue"] is DateTime ? ((DateTime)row["DateFieldValue"]) : DateTime.MinValue,

										  }).ToList();
			}
			catch (Exception ex)
			{
			}

			return listOfResultItems;
		}

But when we add this: .ToLocalTime() to our datetime the date is converted to correct date and time

DateFieldValue = !(row["DateFieldValue"] is DBNull) && row["DateFieldValue"] is DateTime ?
((DateTime)row["DateFieldValue"]).ToLocalTime() : DateTime.MinValue,