Method system.datetime parse(system.string) has no supported translation to SQ

Question

0.00/5 (No votes)

See more:

Hello
I Want Runnig This Code but Error This Message "Method 'System.DateTime Parse(System.String)' has no supported translation to SQL"
I Must Not Changed Definiation Of Table But Compare Field Of Table By On Veriable Datetime
Regrads
Yarian

Code C# :

C#

DataClasses1DataContext db = new DataClasses1DataContext();

C#

DateTime D = new DateTime();
D = DateTime.Now;
var query = db.Tables.Where(x => DateTime.Parse(x.DT) > D).ToList();
foreach (var item in query)
{
    MessageBox.Show(item.DT);
}

Schem Of Table :

SQL

CREATE TABLE [dbo].[Table] (
    [DT] NVARCHAR (50) NULL
);

What I have tried:

IAm Change Field From varchar(50) To nvarchar(50)
But Iam Not Should change To Datetime

Posted 22-Aug-16 6:42am

misaqyrn9677

Updated 22-Aug-16 13:19pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

David_Wimbley · Answer 1 · 2016-08-22T06:51:00

Without knowing more about your problem/the format of your dates i think you have 2 options

1) Convert your DateTime.Now variable to the string formation to do your comparison. Although i don't think this will accomplish what you are wanting.

Ex:

var d = DateTime.Now.ToString("yyyyMMdd")

2) Place the .ToList before the .Where clause of your query. The issue is that the .ToList is the command to execute against the database and there is no Linq to entities conversion for DateTime.Parse.

If you were to do the following

C#

var query = db.Tables.ToList().Where(x => DateTime.Parse(x.DT) > D);

Then it would execute against the database prior to trying to translate DateTime.Parse to SQL. However this could create a lot of problems should you be dealing with a lot of data as this reads the entire Tables table into memory prior to filtering.

I would not suggest this but given that you say you cannot change this schema. This would accomplish what you are asking for.

Philippe Mori · Answer 2 · 2016-08-22T13:19:00

I would say that you should change the database column anyway... You might try to find a way to update all your application.

For example, you might add a new nullable column for date written in proper format and then update all update to update both columns. Once all applications are updated, then you can manually update any item that is still null from the text column. Once this is done, you can make that column required (if necessary). At that point, you can update all code that read the database to read the new column. Once this is done, you can remove the old text column.

Then from that point you won't have to deal with the improper column type anymore and it will simplify any ew code that read or update the database.

If the application is deployed at many locations (for example, on client computer), then you could update the database when a new version of your application open a database that still use old column.

If it is really impossible to do that, then I hope that at least the text column is formatted in year, month and day order as otherwise it would be a pain to do any comparison.

You might also consider doing a stored procedure or manually write SQL code that would fix the problem. It might also be possible to do with Linq and SubString but I am not sure.

Method system.datetime parse(system.string) has no supported translation to SQ

2 solutions

Solution 1

Solution 3

Add your solution here

Preview 0