Subsonic How to select between date

Question

Could anyone tell me how to do the select function in SubSonic project to query all customer who will have birthday in next two week from today.

Table Customer Name Thomas DOB 19/09/1981

Thank you

Answer 1

Please try this:

new Select("Provider").From("Customers")
.Where("CustomerName")
.IsEqualTo("Thomas")
.Where("DOB")
.IsBetweenAnd(DateTime.Today, DateTime.Today.AddDays(14));

PS:- Provider is your SubSonic provider name.

Answer 2

If you're only using one provider (most people are) and you want to take advantage of the table structs that SubSonic generates for you:

CustomerCollection customers = DB.Select().From(Customers.Schema)
  .Where(Customers.Columns.CustomerName).IsEqualTo("Thomas")
  .And(Customers.Columns.DOB).IsBetweenAnd(DateTime.Today, DateTime.Today.AddDays(14))
  .ExecuteAsCollection<CustomerCollection>();

Answer 3

I think I need to explain more on what's I would like to have, I would like to send an email to all customer who birthday is in the next 2 weeks in 3 times, 2 weeks before his/her birthday, 3 days before and 1 days before.

Answer 4

In SQL Server, this would be something like

select name, dob 
from customer
where datediff(day,getDate(),dob)+1 = 14 
or datediff(day,getDate(),dob)+1 = 3
or datediff(day,getDate(),dob)+1 = 1

In SubSonic, you could write this like so:

new Select(Customer.NameColumn, Customer.DobColumn)
.From(Customer.Schema)
.Where("datediff(day,getDate(),dob)+1=14")
  .Or("datediff(day,getDate(),dob)+1=3")
  .Or("datediff(day,getDate(),dob)+1=1")

Answer 5

Hi ranomore,

This syntax is not working , there is a warning "Cannot access static method Or in non static context". I am using Subsonic version 2.2

Thanks