CODEWITHSUNDEEP
c#sql-serverlinqentity-frameworkdate
Gaff
Gaff

Reputation: 5667

How to query a date range from a varchar column using LINQ

I have inherited a large SQL Server database that has a certain table that has a certain date column that is a varchar(8) datatype in the yyyyMMdd date string format. Why it isn't a datetime datatype or something similar is beyond me. (maybe to save space? Legacy db ported to SQL Server long ago?)

Withouting getting too flustered over the date wanna-be field, grabbing rows using a single date from this column is pretty straight forward using the Entity Framework (DBContext w/LINQ) as shown below:

var rows = cxt.TableOfRandomDateColumn.Where(x => x.Date == myDateStr).ToList();

That's about as easy as it gets. However, what if I wanted to grab a range of dates from this column? What would be the best way to go about doing something like that?

Upvotes: 0

Views: 737

Answers (3)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Use ParseExact method of datetime with your custom format. Then you can compare it to datetime in your expression.

Upvotes: 0

Randy Minder
Randy Minder

Reputation: 48532

If the dates are in the format you say it is (e.g., 20121025), then they will be treated as already sorted correctly, and the following should work:

var rows = cxt.TableOfRandomDateColumn.Where(x => x.Date >= startDate && x.Date <= endDate).ToList();

Upvotes: 1

Sebastian Meine
Sebastian Meine

Reputation: 11823

Dates in the yyyyMMdd format are alphabetically as well as numerically ordered correctly, so you can just use a range selector as you would for any other string column.

Try this:

var rows = cxt.TableOfRandomDateColumn.Where(x => x.Date >= myStrDateStr && x.Date <= myStpDateStr).ToList();

Upvotes: 5

Related Questions