CODEWITHSUNDEEP
c#linq
FRules
FRules

Reputation: 749

SQL linq convert string to int

I have a table with a varchar timestamp column

id | filename | timestamp
-------------------------------
1  | example  | 20150226145109

Now I need a query which only shows me the entries where the timestamp is older than 7 days.

DateTime date = DateTime.Now.AddDays(-7);
string past = date.ToString("yyyyMMddHHmmss");

Unfortunately, I can't do this now in my linq:

var requests = from request in db.Request
               where Convert.ToInt32(Timestamp) < Convert.ToInt32(past)) 
               select request;

"LINQ to Entities does not recognize the method 'Int32 ToInt32(System.String)' method, and this method cannot be translated into a store expression."

I don't know how I can fix this.

In an earlier version I executed raw sql like this:

SELECT filename, timestamp FROM dbo.Requests WHERE timestamp < 20150219060814

Can anyone tell me how I am able to convert the strings to ints?

Upvotes: 0

Views: 573

Answers (1)

Habib
Habib

Reputation: 223187

Since your column is varchar type and it stores the dates in yyyyMMddHHmmss pattern you can use simple string comparison (instead of converting it to int). Like:

var requests = from request in db.Request
               where db.Timestamp.CompareTo(past) < 0
               select request;

Consider the following sample SQL statement. 

SELECT  1 
where'20150226145109' > '20140226145109'

It will give you 1 back since "string" '20150226145109' is greater than '20140226145109' (one year older date).

Although you should store DateTime in its respective column type, but one of the advantages of your current DateTime format is that it works based on natural string sorting rules.

Upvotes: 2

Related Questions