Filter by Date using partial date string?

Question

I want to be able to filter records by date based on user input, which is a "partial" date string, and as the user types in the date value I want to continue to narrow the number of records returned.  The date search performed by the user will be in the format of yyyy-MM-dd, so by typing "2012" all records from 2012 are returned, by typing "2012-02" all records from Feb. 2012 are returned, and so forth.

What I've tried below but does not work presumably because the ToString() method does not translate into T-SQL?

var records = dbc.Case.Where(c => c.Case_date.Value.ToString().StartsWith(_input))
.OrderBy(c => c.Case_number)
.Select(c => new { c.Case_id, c.Case_number, c.Case_date});

I've also played around a bit using Query Expression syntax and the "let" statement, but only managed to return all records for 2012, and nothing more when a month and day value were typed in addition to the year value. Is there a way to do this like I've described? I would appreciate any suggestions or help, thanks! 

Answer 1

When you say it does not work, what does it do??

I have tried against a database that I have and have no problem with what you are trying to do.  

If you are getting wrong results it may be that what is returned with the toString is not what you expect.  To test this I would just do a simple select of the date with a toString and see what you get.  My toString returns a MMM DD YYYY string.  

Hope this helps

Lloyd Sheen

---

Lloyd Sheen

Answer 2

Hi Lloyd,

I get the same result as you with a simple test:

var record = dbc.Case.Where(c => c.Case_id  == 1).Select(c => c.Case_date.ToString()).FirstOrDefault();
// result is same as yours:  mmm dd yyyy
// but I need to test against this date format: yyyy-MM-dd
// 
var record = dbc.Case.Where(c => c.Case_id  == 1).Select(c => c.Case_date.Value.ToShortDatetimeString()).FirstOrDefault();
// result is in the format I want:  yyyy-MM-dd

var records = dbc.Case.Where(c => c.Case_date.Value.ToShortDateString().StartsWith("2012")).ToArray();
// This throws the NotSupportedException: Method 'System.String ToShortDateString()' has no supported translation to SQL.

I need to be able to select the date in the yyyy-MM-dd format to test against the user input, and the ToShortDateTimeString method does this, but then it bombs when used in conjunction with the StartsWith method...how do I get around this?

Answer 3

I did some testing and the only suggestion I can make is to perform a conversion on the user input so that you can use the following to get what you want: (sorry in VB.NET)

dateList = (From d In dc.RFolders Where d.UpdateDate.Value.Year.ToString = "2001" Select xx = d.UpdateDate.ToString() Take 10).ToList

You can parse the input into year/month/day and expand on what I did to have a compound where statement for each of the elements of the date.  The area of dates is one that does not seem well supported in SQL (all platforms) so I think you are stuck with the solution above.

If you are using databinding this would be a great place for a converter.  The property that you would bind to would be an object with year/month/day properties.  In the converter you would take the input and "convert" it into the components of the above object.  Then in the code to use Linq you would use the properties of the bound object rather than what was input.  Hope this makes sense.

Just remembered that the property you would bind to needs to be a dependency property most likely of type date.

Lloyd Sheen

---

Lloyd Sheen

Answer 4

Thanks, I'll have a go at this and post back.

Answer 5

I've tried but have been unable to get it to work quite like you suggested. I may just remove the search date option and and simply return a list of all dates which will never be more than 20 items so not that big of a deal.  Thanks for all the help and suggestions!

Answer 6

When you say it doesn't work, what does it do????

It should be simple.  

LS

---

Lloyd Sheen

Answer 7

Hi Lloyd,

I didn't mean to suggest your solution doesn't work, only that I'm not able to utilize your approach as I'm not using databinding (or converters).  Switched the record search to search by Date range instead of partial Date string and it is working well.  Thanks for your continued assistance!