How to save weekend values in Database?


I am saving a table company information where I want to save the weekends for that particular company. Thing is when I was doing a payroll management system, I could bring out the total working days in a month. But that was not dynamic. Here is the code,

int totalWorkingDaysInMonth = Enumerable.Range(1, DateTime.DaysInMonth(month.Value.Year, month.Value.Month))
                            .Select(day => new DateTime(month.Value.Year, month.Value.Month, day))
                            .Count(d => d.DayOfWeek != DayOfWeek.Saturday &&
                                        d.DayOfWeek != DayOfWeek.Friday);

I want to get the DayOfWeek Property dynamically from the database. How can I do that? 


Asked by:- SalmanZahir
0
: 145 At:- 5/24/2018 5:29:46 AM
asp.net asp.net mvc c#






2 Answers
profileImage Answered by:- SalmanZahir

UPDATE: 

This is the code for bringing the DayOfWeek property dynamically. Solved it. 

                    int totalWorkingDaysInMonth = 0;
                    var timeTable = db.TimeTableAndOtherSettings
                        .Where(a => a.TimeTableAndOtherSettingsId == data.Employee.TimeTableAndOtherInforamtionsId)
                        .Include(a => a.CompanyInformation).ToList();
                    foreach (var timetabledata in timeTable)
                    {

                        totalWorkingDaysInMonth = Enumerable
                            .Range(1, DateTime.DaysInMonth(month.Value.Year, month.Value.Month))
                            .Select(day => new DateTime(month.Value.Year, month.Value.Month, day))
                            .Count(d => d.DayOfWeek != (DayOfWeek)Convert.ToInt32(timetabledata.WeekEnd1) &&
                                        d.DayOfWeek != (DayOfWeek)Convert.ToInt32(timetabledata.WeekEnd2));
                    }
1
At:- 5/24/2018 7:25:05 AM
Thanks for updating your answer, if you have solved it :) 0
By : vikas_jk - at :- 5/24/2018 7:47:27 AM


profileImage Answered by:- vikas_jk

I am not sure if I got this right, but you can get Day of the week by using DATENAME and DATEPART in sql server, then remove that paticular days from the list or save it in database.

Suppose, if you have DateTime saved in the database and want to check if that date is weekend value or not

SELECT DATENAME(dw,DateTimeColumn) -- Saturday
SELECT DATEPART(dw,DateTimeColumn)) -- 7

In C# linq

using System.Data.Objects.SqlClient; //Don't forget this!!

//You can access to SQL DatePart function using something like this:

YourTable.Select(t => new { DayOfWeek = SqlFunctions.DatePart("weekday", t.dateTimeField) - 1 });
//t.dateTimeField is the column with datetime value to check

//You can compare to SQL DatePart function using something like this:

DateTime dateToCompare = DateTime.Today;
YourTable.Where(t => SqlFunctions.DatePart("weekday", t.dateTimeField) - 1 == dateToCompare }); //Zero based in SQL

If you are using Entity framework check this

https://stackoverflow.com/questions/40271588/entity-framework-dayofweek

To Get the total number of working days for a month of specific period of time, here is the complete C# program

using System;
					
public class Program
{
	public static void Main()
	{
		

       int totalWorkingDaysInMonth = GetWorkingDays(DateTime.Now.AddDays(-31),DateTime.Now);
                            
		
		Console.WriteLine(totalWorkingDaysInMonth);

	}
	
	public static int GetWorkingDays(DateTime dtmStart, DateTime dtmEnd) 
    { 
		// This function includes the start and end date if it falls on a weekday 
		int dowStart = ((int)dtmStart.DayOfWeek == 0 ? 7 : (int)dtmStart.DayOfWeek); 
		
		int dowEnd = ((int)dtmEnd.DayOfWeek == 0 ? 7 : (int)dtmEnd.DayOfWeek); 
		
		TimeSpan tSpan = dtmEnd - dtmStart; 
		
		if (dowStart <= dowEnd) 
		{ 
		return (((tSpan.Days / 7) * 5) + Math.Max((Math.Min((dowEnd + 1), 6) - dowStart), 0)); 
		} 
		else 
		{ 
		return (((tSpan.Days / 7) * 5) + Math.Min((dowEnd + 6) - Math.Min(dowStart, 6), 5)); 
		} 
    } 
}

Try : https://dotnetfiddle.net/1X2nME

Output: 24

0
At:- 5/24/2018 7:46:03 AM Updated at:- 5/24/2018 7:46:37 AM





Login/Register to answer
Or
Register directly by posting answer/details

Full Name *

Email *




By posting your answer you agree on privacy policy & terms of use