Why I can't use ToShortDateString on asp.net

Question

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

Please provide the LINQ query that makes troubles. ASP.NET is not related to your issue. — Tim Schmelter, Dec 05 '13 at 07: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. — Jensen, Dec 05 '13 at 07:56
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. — Richard, Dec 05 '13 at 08:01

ps2goat · Accepted Answer · 2013-12-05T08:12:24.537

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()
                         };

This should be the answer. Also there are lots of such questions and answers http://stackoverflow.com/questions/163183/linq-to-sql-peculiarities. Jerald, do not forget about google search! — Artur A, Dec 05 '13 at 08:20

Raghubar · Answer 2 · 2013-12-05T09:10:24.110

-1

Try This.

CreatedDateTime = DateTime.Parse(d.CreatedDateTime.ToString()).ToShortDateString();

edited Dec 05 '13 at 09:10

answered Dec 05 '13 at 08:06

Raghubar

2,768
1
21
31

I got this error `The best overload method match for 'System.DateTime.Parse(string)' has some invalid arguments` – JeraldPunx Dec 05 '13 at 08:10
Add To.String() in d.CreatedDateTime.ToString(). I have also edited my code. check. – Raghubar Dec 05 '13 at 09:09
This is not the answer until the op has local objects to work with. – ps2goat Dec 05 '13 at 16:13

Why I can't use ToShortDateString on asp.net

2 Answers2