Category: LINQ

03 Apr 2015 12:39

Joined:	03/01/2012
Level:	Silver
Points:	204

Points:	5

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

UserCompanies
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 :
Hari

#757614

03 Apr 2015 23:20

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	4

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

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

#760676

17 Jul 2015 03:36

Joined:	21/01/2014
Level:	Silver
Points:	134

Points:	4

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

Thanks
Umesh Bhosale

How to get Sum of days between to dates using linq

About dotnetspider.com

Quick Links

General

Services