0

I'm using Excel as database, I want to get data filtering by current week (from Monday to Sunday) using C# (sql Query).

exemples:

if I select a day for ex: 8 Monday,02,2016 ... will return all data has 'DATE' from (8 Monday) to (14 Sunday) . but if I select a day for ex: 20 Saturday,02,2016 ... will return all data has 'DATE' from (15 Monday) to (21 Sunday)

Thanks :)

Otman Timo
  • 1
  • 2
  • 1
    Using Excel as DB is a terrible approach. It is data analyze tool not suitable for serving as DB. You can get SQL Express for free. Worst case switch to CSV or JSON. This will save you from dealing with weird Excel stuff as well. – IgorM Feb 13 '16 at 22:40
  • Does this need to be done with C# code or SQL? – Nick Feb 13 '16 at 22:55
  • if it can be done with sql it will be better – Otman Timo Feb 13 '16 at 22:58
  • What do you have in place for code right now? Are you already reading in the data and looping through it? Or are you not there yet? – Nick Feb 13 '16 at 23:16
  • I have done something but in c# ... I read all data and a filtered with BindingSource – Otman Timo Feb 13 '16 at 23:19
  • Ultimately, what you are trying to do is to determine if the record from the Excel file should be used based on the date falling within the current week. If this is the case, you certainly could do this in SQL, but I'm not sure the advantage as you could do this just as easily in C# and not need to connect to the database, etc. On the other hand, you could use SQL for all of this by simply importing all the Excel records into a staging or temp table, then running SQL queries on that data to get the records you want. Which direction would you feel most comfortable with? – Nick Feb 13 '16 at 23:23
  • I put all the data from Excel and a put it in a DataTable, and I used a BindingSource To filter what I want – Otman Timo Feb 13 '16 at 23:27
  • this is my code DateTime input = DateTime.Now; int Alpha = DayOfWeek.Monday - input.DayOfWeek; DateTime monday = input.AddDays(Alpha-1); DateTime Sunday; if (input.DayOfWeek != DayOfWeek.Sunday) { Sunday = input.AddDays(Alpha + 6); } else { Sunday = input; } bs_F.Filter = string.Format("Date <= '{0}' and Date >= '{1}'",Sunday , monday); – Otman Timo Feb 13 '16 at 23:27
  • Will you ultimately be inserting the selected data records into a SQL table? If not, why do you need SQL at all? – Nick Feb 13 '16 at 23:29

1 Answers1

0

If you are going to do this in C#, you need 2 steps:

1) Find the start and end dates of the current week. I like this solution: How can I get the DateTime for the start of the week? For the end date, you simply add 6 days to it.

2) Determine if your date from the record is within the start and end dates, like so: input >= startDate && input <= endDate

I assume you are dealing with dates and not datetime/timestamps. If not, you will need to tweak it to be more inclusive.

Community
  • 1
  • 1
Nick
  • 7,103
  • 2
  • 21
  • 43