You must Sign In to post a response.
  • Category: LINQ

    How to get Sum of days between to dates using linq

    Are you looking for a way to get Sum of days between to dates using linq ? then read this thread to know how to get it

    i have two tables one is UserTable and another is Usercompanies Table

    UserTable field
    1. UserId
    2. UserName
    3. Graduation

    1. Id
    2. UserId
    3. Company Name
    4. Joining Date
    5. Leaving Date
    5. Currently Working

    i want to select those users which have to more then 2 years experience on basis of joining and leaving date. user companies may be more then one and user can currently working in maximum 3 companies.
    UserId of UserTable is foreigen key in UserCompanies Table

    so please suggest me query for that

    Thanks & Regards :
  • #757614
    With the help of DateDiff function in SQL you can get the difference between joining date and leaving date, if the difference we get in years then we can calculate number of years experience
    see below snippet

    1.Select UserId, Joining date, DATEDIFF(YY,Joining Date,Leaving Date) AS NumberOfYears FROM UserCompanies

    Run the below query and notice the output below the query –
    1.Select UserId, DateOfBirth, DATEDIFF(YY,Joining Date,Leaving Date()) AS NumberOfYears,(CONVERT(int, CONVERT(varchar, Leaving Date, 112)) - CONVERT(int, CONVERT(varchar, Joining Date, 112)))/10000 AS RevisedNumberOfYears FROM UserCompanies

    hope it helps

    Editor, DotNetSpider MVM
    Microsoft MVP 2014 [ASP.NET/IIS]

  • #760676
    Hi Hari
    If you going to do this using LINQ then Follow Following steps

    write Following Class

    class User
    public int UserId { get; set; }
    public string UserName { get; set; }
    public string Graduation { get; set; }

    class UserCompanies
    public int Id { get; set; }
    public int UserId { get; set; }
    public string CompanyName { get; set; }
    public DateTime JoiningDate { get; set; }
    public DateTime LeavingDate { get; set; }
    public string CurrentyWorking { get; set; }

    and Take data into collection of respective class
    say List<User> lstUser
    List<UserCompanies> lstUserCompnies

    and Write Follwing Query
    var ss = from x in lstUser
    join y in lstUserCompnies
    on new { x.UserId } equals
    new { y.UserId }
    where y.LeavingDate.Subtract(y.JoiningDate).TotalDays >= 730
    select new { x.UserName, x.Graduation };

    Hope it will Help

    Umesh Bhosale

  • Sign In to post your comments