Question Show data that is within a certain date range

[Monkey718]

So I am trying to make an application that is for finances. I want to display the sum of numbers stored in a sql database that fall within a range. So within a year, week, and day. I cannot seem to figure this out or how to get within a year. Any ideas on how to go about this?

 

[jmcilhinney]

This sounds like a purely SQL question. If the question doesn't involve C# code then it's not really a C# question. We have forums dedicated to Database questions so I have moved this question there.

Filtering records on a date range is really no different to filtering on a numeric range. Just as a numeric value is greater than or equal to the minimum and less than or equal to the maximum, so too is a date value, e.g.

SELECT * FROM Person WHERE DateOfBirth >= @StartDateOfBirth AND DateOfBirth <= @EndDateOfBirth

You insert the parameter values into the SQL the same way as you would any other parameters, e.g.

myCommand.Parameters.AddWithValue("@StartDateOfBirth", startDateOfBirthPicker);
myCommand.Parameters.AddWithValue("@EndDateOfBirth", endDateOfBirthPicker);

 

[Monkey718]

So I understand why your saying it’s sql, but I understand how to filter the sql results. My main problem is for example getting the amount they earned for the current week or a week they choose. That’s all c# because I don’t know how to get the current week or even month. Does that make sense? I’m not sure if I am explaining this correctly! I am sorry!

 

[jmcilhinney]

That’s all c# because I don’t know how to get the current week or even month. Does that make sense?

It does, but if the actual question you wanted answered was how to get the current week or the current month then that's the question you should have asked. There's nothing in your original post that actually indicates that you want data relative to the current date, just for an arbitrary date range. If what you really want to know is how to get the date range then what you want to do with it is of incidental interest at best, because how you get it isn't affected by that. If you don't ask the question you need answered then you're unlikely to get the answer you need..

Anyway, the DateTime type can give you all the information you need:

var currentDate = DateTime.Today;

var currentWeekStart = currentDate.AddDays(DayOfWeek.Sunday - currentDate.DayOfWeek);
var currentWeekEnd = currentDate.AddDays(DayOfWeek.Saturday - currentDate.DayOfWeek);
// var currentWeekEnd = currentWeekStart.AddDays(6);

var currentMonthStart = currentDate.AddDays(1 - currentDate.Day);
// var currentMonthStart = new DateTime(currentDate.Year, currentDate.Month, 1);
var currentMonthEnd = currentDate.AddDays(DateTime.DaysInMonth(currentDate.Year, currentDate.Month) - currentDate.Day);
// var currentMonthEnd = new DateTime(currentDate.Year, currentDate.Month, DateTime.DaysInMonth(currentDate.Year, currentDate.Month));

var currentYearStart = currentDate.AddDays(1 - currentDate.DayOfYear);
// var currentYearStart = new DateTime(currentDate.Year, 1, 1);
var currentYearEnd = currentDate.AddDays((DateTime.IsLeapYear(currentDate.Year) ? 366 : 365) - currentDate.DayOfYear);
// var currentYearEnd = new DateTime(currentDate.Year, 12, DateTime.DaysInMonth(currentDate.Year, 12));

As you can see, there's more than one way to get to some of the values. Note that the DayOfWeek enumeration goes from Sunday with a value of 0 to Saturday with a value of 6. The week calculations that use DayOfWeek are based on Sunday being the first day of the week and use those values. If you want to work on the basis that Monday is the first day of the week then you can simply add 1 day to the start and end dates of the week.

Note that I've moved this thread back to the C# General forum.