DATEDIFF how to write in Asp.net MVC

Question

I am creating a car retail system. I need to the display if I enter the correct carid relevant car information will be displayed in the below text boxes. I have attached the screen shot image below. I need to calculate day difference between start date and end date for calculation of how many days between because retail fee needs to be calculated for extra days of the retail system. In C# i wrote like this select car_id,cust_id,due,DATEDIFF(GETDATE(),due) as elap from rental where car_id = ? I don't know how to write in Asp.net MVC.

Code I tried:

form design

<div class="row">
    <div class="col-sm-8">

        @using (Html.BeginForm("Save", "return", FormMethod.Post, new { id = "popupForm" }))
        {
            <div>
                <h4> Registation</h4>
            </div>

              <div class="card-action">

                <label class="form-label">Car ID</label>
                  <input type="text" id="carno" name="carno" class="form-control" placeholder="carno" required />
            </div>



            <div class="card-action">

                <label class="form-label">Customer ID</label>

                <input type="text" id="custid" name="custid" class="form-control" placeholder="Customer ID" required />

            </div>



            <div class="card-action">

                <label class="form-label">Date</label>

                <input type="text" id="date" name="date" class="form-control" placeholder="Date" required />

            </div>


             <div class="card-action">

                <label class="form-label">Days Elapsed</label>

                 <input type="text" id="elap" name="elap" class="form-control" placeholder="Date" required />

            </div>


             <div class="card-action">

                <label class="form-label">Fine</label>

                 <input type="text" id="fine" name="fine" class="form-control" placeholder="Fine" required />

            </div>


            <div class="card">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>

        }
    </div>

</div>

jQuery seach the data using jquery

   <script>
        getProductcode();
        function getProductcode() {
            $("#carno").empty();
            $("#carno").keyup(function (e)
            {
                var q = $("#carno").val();
                $.ajax({
                    type: "POST",
                    url: '/return/Getid?carno=' + $("#carno").val(),
                    dataType: "JSON",
                    success: function (data)
                    {
                        console.log(data);
                          $('#custid').val(data.custid);
                          $('#date').val(data.sdate);
                           $('#elap').val(data.elap);

                    },
                    error: function (xhr, status, error)
                    {
                        //  alert("The barcode entered is not correct");
                    }
                });
                return true;
            });
        }

    </script>

returnController

 [HttpPost]
        public ActionResult Getid(String carno)
        {
            carrentEntities1 db = new carrentEntities1();
            var carn = (from s in db.rentails where s.carno == carno select s.custid).ToList();
            return Json(carn, JsonRequestBehavior.AllowGet);
        }

I don't know how to write the Datediff to calculate days. In this no result displayed. I tested custid, it didn't display.

Database fields

id  carno   custid  fee   sdate      edate
1   1        1     1200  2019-12-09  2019-12-19
2   1        1     20000 2019-12-01  2019-12-31
3   A0001    1     3434  2019-12-09  2019-12-27

Eldar · Accepted Answer · 2019-12-09T13:13:12.943

2

You can use SqlFunctions.DateDiff like this :

   var carn = (from s in db.rentails where s.carno == carno 
       select new {   
       StartDate = s.sdate,
       EndDate = s.edate,
       CarNo = s.carno,
       Fee = s.fee,
       ElapsedDays = SqlFunctions.DateDiff("day",DateTime.Now,s.sdate)
    }).ToArray();

Document.

edited Dec 09 '19 at 13:13

answered Dec 09 '19 at 12:13

Eldar

9,781
2
10
35

var carn = (from s in db.rentails where s.carno == carno select s.custid).Select(t=> s.sdate { ElapsedDays = SqlFunctions.DateDiff("day",DateTime.Now,t.edate) }).ToArray(); error displayed – Java fiver Dec 09 '19 at 12:18
@Javafiver i forgot to put `new` keyword. And your syntax is not correct. See [anonymous types](https://www.tutorialsteacher.com/csharp/csharp-anonymous-type) – Eldar Dec 09 '19 at 12:23
var carn = (from s in db.rentails where s.carno == carno select s.custid).Select(t=> new { // here how to select start date ElapsedDays = SqlFunctions.DateDiff("day",DateTime.Now,t.sdate) }).ToArray(); – Java fiver Dec 09 '19 at 12:31
start sdate enddate edate this database table colums – Java fiver Dec 09 '19 at 12:32
@Javafiver made a valid select statement. – Eldar Dec 09 '19 at 12:44
var carn = (from s in db.rentails where s.carno == carno) bracked closed error – Java fiver Dec 09 '19 at 13:04
Y date diplayed on the textbox look like this /Date(1577385000000)/ – Java fiver Dec 09 '19 at 13:26
@Javafiver well that's another question but the workaround described [here](https://stackoverflow.com/questions/726334/asp-net-mvc-jsonresult-date-format) – Eldar Dec 09 '19 at 14:02
Y date diplayed on the textbox look like this /Date(1577385000000)/ diplayed on the textbox i need to diplay 2019-12-9 – Java fiver Dec 09 '19 at 14:03
if you write it is helpful for me . pls only this EndDate = s.edate, – Java fiver Dec 09 '19 at 14:05
EndDate = new Date(parseInt(s.edate.replace("/Date(", "").replace(")/", ""), 10)), i wrote like this but error – Java fiver Dec 09 '19 at 14:08
@Javafiver It should be in your javascript code in your jquery success method : ` $('#date').val( new Date(parseInt(data.sdate.substr(6))).toLocaleDateString());` – Eldar Dec 09 '19 at 14:12

Max · Answer 2 · 2019-12-09T12:43:33.417

1

In C# you don't need to write Datediff() function like in SQL. You just can minus needed dates:

var carn = (from s in db.rentails where s.carno == carno select s).
.ToList()
.Select(x => new 
{
DateDiff = (x.edate - x.sdate).Days,
//rest props
});

edited Dec 09 '19 at 12:43

answered Dec 09 '19 at 12:21

Max

1,784
2
19
26

DateDiff = (x.edate - x.sdate).Days, this line x.edate - x.sdate getting error sir – Java fiver Dec 09 '19 at 12:35
var carn = (from s in db.rentails where s.carno == carno select s). this error indicate error – Java fiver Dec 09 '19 at 12:55

score 0 · Answer 3 · answered Dec 09 '19 at 12:49

0

Pls give it a try the following code:

var carn = (from s in db.rentails where s.carno == carno select s)
.Select(x => new 
{
  DateDiff = (x.edate - x.sdate).Days
});

Note: Make sure type of eDate, sDate is DateTime.

answered Dec 09 '19 at 12:49

Nguyễn Văn Phong

13,506
17
39
56

DateDiff = (x.edate - x.sdate).Days in this line Days error – Java fiver Dec 09 '19 at 12:54
Maybe you have not used namespace for that ? Becuz it works fine for me. – Nguyễn Văn Phong Dec 09 '19 at 12:56
Days what is namespaces to add – Java fiver Dec 09 '19 at 13:01

DATEDIFF how to write in Asp.net MVC

3 Answers3