DateAdd and DatePart Functions

Starting with version 1.1.0 of FluentSql DateAdd and DatePart Sql functions are available for usage in the Where clause. They are accessible through public static methods of the SqlFunctions class. These functions were designed to take entity types as parameters. The following are a list of the available functionality:

DateAdd Sql Functions

• AddYears
• AddQuarters
• AddMonths
• AddDayOfYear
• AddDays
• AddWeeks
• AddDayOfWeek
• AddHours
• AddMinutes
• AddSeconds
• AddMilliseconds

DatePart Sql Functions

• GetYear
• GetQuarter
• GetMonth
• GetDayOfYear
• GetDay
• GetWeek
• GetWeekDay
• GetHour
• GetMinute
• GetSecond
• GetMillisecond

Examples

The SelectStatementTest.cs file has an extensive list of DatePart and DateAdd functions. The following are few of these tests:

Filtering by Month:

 var store = new EntityStore(_dbConnection);
 var singleEmployee = store.GetSingle<Employee>(e => SqlFunctions.GetMonth(e.Birthdate) <= 7);

Filtering by Year:

 var store = new EntityStore(_dbConnection);
 var singleEmployee = store.GetSingle<Employee>(e => SqlFunctions.GetYear(e.Birthdate) <= DateTime.Now.Year);

Filtering by using AddMonths function:

 var store = new EntityStore(_dbConnection);
 var singleEmployee = store.GetSingle<Employee>(e => SqlFunctions.AddDays(e.Birthdate, -3) >= DateTime.Now.AddMonths(-90 * 12));