I came across a weird issue this morning whilst debugging a Web Part. I had a list that stored a date in one column and a Calculated Column that returned a date after applying a formula.
The regional settings of the site were set to English (Australia) – see screenshot below.
My calculated column was set up like so (formula and columns blurred out) -
Looking at the list everything appeared fine and the dates were displayed in the correct format (dd/MM/yyyy). Expiry is the calculated column.
However when accessing the list items programmatically and trying to convert the Expiry date into a DateTime it was throwing an exception. The dates being returned programmatically were in the below format -
This is clearly an invalid date when compared to the culture of my machine (UK).
What I ended up doing was converting the date using US culture (en-US) and then changing the format before converting back into a DateTime.
Below is the code snippet that I used to do this – has anyone seen this issue before? or does SharePoint always store the dates for calculated columns in the Content DB as US format?
IFormatProvider provider = new CultureInfo("en-US");
DateTime date = Convert.ToDateTime(myItem["DateColumn"].ToString(), provider);
string convertedDate = date.ToString("dd/MM/yyyy");
DateTime date1 = Convert.ToDateTime(convertedDate);
I would love to hear back from anyone who has seen this before or can shed any light on it.