Get Month of out System Datetime

I have data in my database that contains the following filed.

Id | name | RegDate
1    John   2014-09-05
2    mike   2014-09-05
3    Duke   2014-10-14

I'm performing a query to count the number of values where the reg date is equal 09. 09 is the month of the date.

I'm trying to convert the date I store in db in a month format then get a new month of a system date to get the result.

Here is my query in linq but it keeps on giving the wrong count. please I need your help. thanks. 

var CountPassengers = (from c in db.CountPassengerManifestViews where c.DepartureDate.Month.ToString()== DateTime.Now.AddMonths(-1).ToString("MM")  select c).Count();
Jon Skeet
people
quotationmark

I would strongly recommend that you get rid of all the string manipulation. Your query doesn't conceptually have anything to do with strings, so why are you introducing them into the code?

You can write your query as:

int currentMonth = DateTime.Today.AddMonths(-1).Month;

var passengerCount = db.CountPassengerManifestViews
                       .Count(c => c.DepartureDate.Month == currentMonth);

However, that will only filter by month - it won't filter by month and year, so if you have data from 2013 that would be included too. It's more likely that you want something like:

DateTime oneMonthAgo = DateTime.Today.AddMonths(-1);
DateTime start = oneMonthAgo.AddDays(1 - oneMonthAgo.Day);
DateTime end = start.AddMonths(1);

var passengerCount = db.CountPassengerManifestViews
                       .Count(c => c.DepartureDate >= start &&
                                   c.DepartureDate < end);

That way you're expressing a range of dates, rather than just extracting the month part.

people

See more on this question at Stackoverflow