Filtering in SharePoint 2007 using [Today] and a String field

1
votes

I have a C# application that inserts data weekly from a CSV flat file to a SharePoint list with over 30000 records in it and growing.

Management wants to see only the items that are under 90 days old in a view, but the date field is being inserted as a single line of text (otherwise it won't upload at all). Right now I have tried creating a new calculated column using =TEXT(DateField,"yyyymmdd") and setting that field to a DateTime type. I then tried to filter on that new column in the view using "NewColumn Greater Than [Today]-90" but that returns zero results.

Is there a way to convert my text field into a date field that can be used to filter like this, or perhaps a way to convert the flat file string into a DateTime value before I even upload to SharePoint?

1
c#sharepointsharepoint-2007
What do you mean by 'otherwise it won't upload at all'? - banana
When I set the field type as Date and Time on the list, the upload function returns an invalid format error, even when I attempt to insert a DateTime object from C#. I have heard of functions that could potentially be used to avoid this issue, but they require the development environment to be located on the same server as the SharePoint site in order to reference the DLL, which is not possible in my situation. - Charles A

1 Answers

0
votes

I ended up finding an answer.

SharePoint would not let me filter on fields that were converted in this way, so I had to upload the data already in the correct format. The CSV feed was giving me a string in the "yyyyMMdd" format, but SharePoint needs hyphens between the format blocks like: "yyyy-MM-dd". The FieldRef node inside your batch element also needs to look like this:

"<Field Name='Date_x0020_Field' Type='DateTime' Format='DateOnly'>" + date + "</Field>"

I ended up converting to a DateTime object and then back to string, because I was integrating some other data already in a datetime format, but you could just as easily insert the hyphens manually.

You can convert a string to DateTime with Convert.ToDateTime(stringDate); and you can convert to the proper format from that using dateTimeObject.ToString("yyyy-MM-dd");