This follows on from the article "How do you set a null value via (SOAP) Web Services?".

Part of the difficulty in setting DateTime fields to null via webservices is that System.DateTime in the (Common Language Runtime) CLR is considered a value type. In .NET, value types, including int, double, short etc. cannot be assigned to null. This applies equally to DateTime.

We can try setting it to null using the System.DBNull which represents a nonexistent value.

System.DBNull myNull;
myNull = System.DBNull.Value;
CRMCompany.uploaddate = System.DateTime.Parse(myNull.ToString());

This compiles but generates an error at run time because "String was not recognized as a valid DateTime."

But what about retrieving records with null datetime values?

If a field is null then that field is not returned within the XML for the retrieved record. For example if we use QueryEntity to retrieve a company record and the comp_status (String) or comp_uploaddate (DateTime) are null the those fields are not described in the XML that is returned. They are however described in the Company class as it is described in the WSDL so the field can be used as a property of the instantiated company object. But if we then try and use a datetime fields that does not contain a value the default of "01/01/0001 00:00:00" is returned.

MessageBox.Show(CRMCompany.uploaddate.ToString());

This will return "01/01/0001 00:00:00" if the field is null.

We have seen that we can't set a DateTime to null and we can't use the trick (that can be used with other datatypes) of setting it to an empty string ("") because the string has to be correctly parsed into a DateTime value.

So it looks like we will have to rely on both Sage CRM and the database agreeing a certain date as a null date.

It may look like we could use "01/01/0001 00:00:00" as this is the minimum date that C# can describe.

This can be set programmatically like this:

CRMCompany.uploaddate = System.DateTime.MinValue;

This compiles but generates the following SQL error at run time

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value

This is because it attempts to set the DateTime field to "comp_uploaddate='00010101 00:00:00'".

The minimum year value that MS SQL can hold is 1753. Why? This is all done to the way Microsoft has decided to handle the Julian/Gregorian calendar switch over. You'll need to Google to learn more about that.

This means that the minimum date to be used to represent a null have to be later.

You could use

CRMCompany.uploaddate = (System.DateTime)System.Data.SqlTypes.SqlDateTime.Null;

Which will set the DateTime field to be "01/01/1900 00:00:00" BUT this has a problem in that Sage CRM will recognise this as a date and will display it.

Therefore in order to set a psuedo null value to a DateTime field we need to use a date that is not displayed by Sage CRM and can be treated as a null. This is the date "30/12/1899 00:00:00".

CRMCompany.uploaddate = System.DateTime.Parse("30/12/1899 00:00:00");

DateTime fields in screens and columns within the Sage CRM user interface will then appear to be empty.

The ideal date format is yyyy-MM-ddTHH:mm:ss. When settings a recordfield value you can use someDate.ToString("yyyy-MM-ddTHH:mm:ss") where someDate is your datetime variable.