Member Avatar for korathualex

How to convert date format to dd/MM/yyyy in Linq to Sql select Query

I tried as below:

DataTable dt = Common.dc.TblMaintenances.Where(a => a.ContractId == Id && a.IsScheduled == true || a.IsScheduled == false).OrderBy(a => a.ScheduledDate).Select(a => new { a.MaintenanceId, a.MaintenanceDate.ToString("dd.MM.yy"), a.MaintenancePerson,a.Remarks,a.ScheduledDate,a.IsScheduled,a.Cost }).getDataTable();

But didn't work

Edited by pritaeas because: Added markdown
  • 3 Contributors
  • 2 Replies
  • 1K Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by pixelsoul

Dani AI

Generated

Quick summary for : the LINQ provider tries to turn your Select expression into SQL. A call like DateTime.ToString("format") with a format string cannot be translated into SQL, so it fails inside the query. Do the formatting after the query has been materialized (or in the UI), or perform the formatting in SQL (as suggested) if you must have the string come from the DB. (stackoverflow.com)

A safe pattern is: query the DateTime as a DateTime (so it stays translatable), materialize the results, then format locally. Example:

var rows = Common.dc.TblMaintenances
    .Where(a => a.ContractId == Id)             // keep server-side filters here
    .OrderBy(a => a.ScheduledDate)
    .Select(a => new {
        a.MaintenanceId,
        a.MaintenanceDate,
        a.MaintenancePerson,
        a.Remarks,
        a.ScheduledDate,
        a.IsScheduled,
        a.Cost
    })
    .AsEnumerable()                              // switch to LINQ-to-Objects
    .Select(a => new {
        a.MaintenanceId,
        MaintenanceDate = a.MaintenanceDate.ToString("dd/MM/yyyy"),
        a.MaintenancePerson,
        a.Remarks,
        a.ScheduledDate,
        a.IsScheduled,
        a.Cost
    })
    .ToList();

Use AsEnumerable() (or ToList()) to force client-side formatting. (learn.microsoft.com)

Two quick cautions: (1) calling AsEnumerable()/ToList() pulls the filtered rows into memory, so apply server-side filters (and paging) before materializing to avoid large memory use. (2) Your original Where has mixed &&/|| without parentheses and can return unexpected rows — either remove the redundant IsScheduled test or use explicit parentheses / a.IsScheduled != null if you meant “not null.” Operator precedence is significant here. (stackoverflow.com)

If you want, paste the exact error or show the table schema and I will give a tailored code sample (including a safe DataTable conversion).

Recommended Answers

All 2 Replies

Member Avatar for gusano79

What does "didn't work" mean? What results did this code give you?

Member Avatar for pixelsoul

I am not experienced with ASP, but I am experienced in SQL. I know that you can convert a datetime stamp in MSSQL with convert(). So, this may be helpful for what you are trying to do http://msdn.microsoft.com/en-us/library/ms187928.aspx

I use it on my MSSQL queries because the time stamp is in datetime format, so I use that at convert it to a different format.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.