2

I can see here...

I used like this d.PODate.ToString() to display(see image) I can see it using inspect element... I want to display only date not a time and when I try to use d.PODate.ToString("d") it gave me error like this Method 'System.String ToString(System.String)' has no supported translation to SQL and same to d.PODate.ToShortDateString() got error like this Method 'System.String ToShortDateString()' has no supported translation to SQL.

EDIT

this is my GET method

[HttpGet] public Models.SysDataTablePager Get() { NameValueCollection nvc = HttpUtility.ParseQueryString(Request.RequestUri.Query); string sEcho = nvc["sEcho"].ToString(); int iDisplayStart = Convert.ToInt32(nvc["iDisplayStart"]); string sSearch = nvc["sSearch"].ToString(); int iSortCol = Convert.ToInt32(nvc["iSortCol_0"]); string sSortDir = nvc["sSortDir_0"].ToString(); var BranchId = Convert.ToInt32(((wfmis.Global)HttpContext.Current.ApplicationInstance).CurrentBranchId); var Count = db.TrnPurchaseOrders.Where(d => d.MstUser.Id == secure.GetCurrentUser() && d.MstBranch.Id == BranchId).Count(); var PurchaseOrders = from d in db.TrnPurchaseOrders where d.MstBranch.Id == BranchId && d.MstBranch.MstUser.Id == secure.GetCurrentUser() select new Models.TrnPurchaseOrder { Id = d.Id, PeriodId = d.PeriodId, Period = d.MstPeriod.Period, BranchId = d.BranchId, Branch = d.MstBranch.Branch, PONumber = d.PONumber, POManualNumber = d.POManualNumber, //PODate = d.PODate.ToShortDateString(), PODate = d.PODate.ToShortDateString(), //<------ SupplierId = d.SupplierId, Supplier = d.MstArticle.Article, TermId = d.TermId, Term = d.MstTerm.Term, RequestNumber = d.RequestNumber, //DateNeeded = d.DateNeeded.ToShortDateString(), DateNeeded = d.DateNeeded.ToString(), //<------ Particulars = d.Particulars, RequestedById = d.RequestedById == null ? 0 : d.RequestedById.Value, RequestedBy = d.MstUser.FullName, IsClosed = d.IsClosed, PreparedById = d.PreparedById, PreparedBy = d.MstUser.FullName, CheckedById = d.CheckedById, CheckedBy = d.MstUser1.FullName, ApprovedById = d.ApprovedById, ApprovedBy = d.MstUser2.FullName, IsLocked = d.IsLocked, CreatedById = d.CreatedById, CreatedBy = d.MstUser3.FullName, //CreatedDateTime = d.CreatedDateTime.ToShortDateString(), CreatedDateTime = d.CreatedDateTime.ToString(), UpdatedById = d.UpdatedById, UpdatedBy = d.MstUser4.FullName, //UpdatedDateTime = d.UpdatedDateTime.ToShortDateString() UpdatedDateTime = d.UpdatedDateTime.ToString() }; switch (iSortCol) { case 2: if (sSortDir == "asc") PurchaseOrders = PurchaseOrders.OrderBy(d => d.PODate).Skip(iDisplayStart).Take(10); else PurchaseOrders = PurchaseOrders.OrderByDescending(d => d.PONumber).Skip(iDisplayStart).Take(10); break; case 3: if (sSortDir == "asc") PurchaseOrders = PurchaseOrders.OrderBy(d => d.PONumber).Skip(iDisplayStart).Take(10); else PurchaseOrders = PurchaseOrders.OrderByDescending(d => d.PODate).Skip(iDisplayStart).Take(10); break; case 4: if (sSortDir == "asc") PurchaseOrders = PurchaseOrders.OrderBy(d => d.Supplier).Skip(iDisplayStart).Take(10); else PurchaseOrders = PurchaseOrders.OrderByDescending(d => d.Supplier).Skip(iDisplayStart).Take(10); break; default: PurchaseOrders = PurchaseOrders.Skip(iDisplayStart).Take(10); break; } var PurchaseOrderPaged = new Models.SysDataTablePager(); PurchaseOrderPaged.sEcho = sEcho; PurchaseOrderPaged.iTotalRecords = Count; PurchaseOrderPaged.iTotalDisplayRecords = Count; PurchaseOrderPaged.TrnPurchaseOrderData = PurchaseOrders.ToList(); return PurchaseOrderPaged; }

EDIT 2

I got this on my DateTime on my database... enter image description here

Improve this question
4
  • Please reformat your code.. Commented Dec 5, 2013 at 7:55
  • Please provide the LINQ query that makes troubles. ASP.NET is not related to your issue. Commented Dec 5, 2013 at 7:56
  • The problem is not related to ASP.NET. The problem is that your linq query gets translated into SQL. And in SQL, there is no mapping defined for the ToShortDateString() method. You can add a mapping function yourself, which then uses SQL syntax to trim the date you are fetching from your database into a string. Commented Dec 5, 2013 at 7:56
  • 1
    General suggestion: Don't convert from specific types (date, numeric, …) until you actually render the content for the user (ie. in your view). This has two benefits: 1. locales (for output) only need to be handled in that one place; 2. any processing can be done in the proper type rather than processing a string. Commented Dec 5, 2013 at 8:01

2 Answers 2

Reset to default
6

d is coming from the database; it does not exist as a .NET object at this point. You are asking LINQ-to-SQL to do the .ToShortDateString() in the database, for which there is no conversion function.

If you select the d values first and use .ToList() at the end of the query, you can then create your models with the string formatting functions.

E.g.:

// original db query, still returns IQueryable. var dbPurchaseOrders = from d in db.TrnPurchaseOrders where d.MstBranch.Id == BranchId && d.MstBranch.MstUser.Id == secure.GetCurrentUser(); // change db query to local, in-memory object list var localPurchaseOrders = PurchaseOrders.ToList(); // change the local db.TrnPurchaseOrders objects into your Models.TrnPurchaseOrder objects. // kept the d variable because I'm too lazy to change all lines of your code. var PurchaseOrders = from d in localPurchaseOrders select new Models.TrnPurchaseOrder { Id = d.Id, PeriodId = d.PeriodId, Period = d.MstPeriod.Period, BranchId = d.BranchId, Branch = d.MstBranch.Branch, PONumber = d.PONumber, POManualNumber = d.POManualNumber, //PODate = d.PODate.ToShortDateString(), PODate = d.PODate.ToShortDateString(), //<------ SupplierId = d.SupplierId, Supplier = d.MstArticle.Article, TermId = d.TermId, Term = d.MstTerm.Term, RequestNumber = d.RequestNumber, //DateNeeded = d.DateNeeded.ToShortDateString(), DateNeeded = d.DateNeeded.ToString(), //<------ Particulars = d.Particulars, RequestedById = d.RequestedById == null ? 0 : d.RequestedById.Value, RequestedBy = d.MstUser.FullName, IsClosed = d.IsClosed, PreparedById = d.PreparedById, PreparedBy = d.MstUser.FullName, CheckedById = d.CheckedById, CheckedBy = d.MstUser1.FullName, ApprovedById = d.ApprovedById, ApprovedBy = d.MstUser2.FullName, IsLocked = d.IsLocked, CreatedById = d.CreatedById, CreatedBy = d.MstUser3.FullName, //CreatedDateTime = d.CreatedDateTime.ToShortDateString(), CreatedDateTime = d.CreatedDateTime.ToString(), UpdatedById = d.UpdatedById, UpdatedBy = d.MstUser4.FullName, //UpdatedDateTime = d.UpdatedDateTime.ToShortDateString() UpdatedDateTime = d.UpdatedDateTime.ToString() };
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This should be the answer. Also there are lots of such questions and answers stackoverflow.com/questions/163183/linq-to-sql-peculiarities. Jerald, do not forget about google search!
-1

Try This.

CreatedDateTime = DateTime.Parse(d.CreatedDateTime.ToString()).ToShortDateString();
Improve this answer

3 Comments

I got this error The best overload method match for 'System.DateTime.Parse(string)' has some invalid arguments
Add To.String() in d.CreatedDateTime.ToString(). I have also edited my code. check.
This is not the answer until the op has local objects to work with.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.