Query Dates From SQLServer in C#?

Question

I am building a web application that is going to show the upcoming birthdays of a list of people stored in a SQL Server 2008 DB. I can't figure out how to query all records in the DB that are within 30 days of today's date.

Here's what I have so far:

using (HumanResourcesDB db = newH umanResourcesDB(ConfigurationManager.ConnectionStrings["HumanResourcesDB"].ConnectionString))
            {
                DateTime todaysDate = DateTime.Now;

                List<Employee> record =
                (from tab in db.Employees
                 where tab.Birthday between DateTime.Now and todaysDate.AddDays(30) 
                 select tab).ToList();
                this.grdBirthdays.DataSource = record;
                this.grdBirthdays.DataBind();
            }

of course the "between" and "and" don't work that's what I need filled in. I've searched the net for a little while to no avail. Any help?

James Hay · Accepted Answer · 2012-02-22 22:04:23Z

4

Just use a greater than and less than

using (HumanResourcesDB db = newHumanResourcesDB(ConfigurationManager
            .ConnectionStrings["HumanResourcesDB"].ConnectionString))
{
    List<Employee> record = (from tab in db.Employees
     where tab.Birthday >= DateTime.Today
     && tab.Birthday < DateTime.Today.AddDays(31)
     select tab).ToList();

    this.grdBirthdays.DataSource = record;

    this.grdBirthdays.DataBind();
}

Also I should mention that I have used DateTime.Today rather than DateTime.Now because today represents the start of the day as 00:00:00.000. If someones birthday is set to whatever today is at 00:00:00.000 and you have used DateTime.Now (let's assume it's 8:00 in the morning). There birthday will not be included in this range because it is considered before "now".

edited Feb 22, 2012 at 22:04

answered Feb 22, 2012 at 21:43

James Hay

7,3656 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Aaron Bertrand Over a year ago

Then you understand that the end of the range should probably be < AddDays(31) not <= AddDays(30) - if someone's birthday is not set to midnight on the last day, they'll get excluded for the same reason.

broguyman Over a year ago

I tried that earlier it gives a few errors. "The type or namespace name 'AND' could not be found" "A local variable named 'tab' cannot be declared in this scope because it would give a different meaning to 'tab', which is already used in a 'child'scope to denote something else"

James Hay Over a year ago

@bromanguy Try to uncapitlize(spelling?) the AND make it lower case.

broguyman Over a year ago

@JamesHay same error. Am I missing a library referenece or something?

James Hay Over a year ago

Well I did copy and paste but then I deleted everything on that line after "where tab.Birthday". From there my SQL typing instinct kicked in and the rest is history.

|

Collectives™ on Stack Overflow

Query Dates From SQLServer in C#?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related