Caml to get today's birthdays

Question

I have a list with birthday field (DateTime).

How do I write caml query to retrieve only the records where birthday falls on today (i.e. only comparing day and month value of birthday column to today's day and month)

Answer 1

It turns out, there is no simple way to achieve this. However, I can suggest at least three quite acceptable approaches.

1 - "This Year Birthday" calculated column

You can consider creating "This Year Birthday" calculated field, use it in the CAML query and compare to <Today /> element:

<Eq><FieldRef Name='ThisYearBirthday' /><Value Type='DateTime'><Today /></Value></Eq>

But since calculated columns are updated only if the item is updated or if the field is updated, you should also create the corresponding timer job to update this field two times a year to get the calculated column refreshed with values for the next year.

This solution is not very elegant, I admit, but it is 100% workable (actually we're using it in our enterprise product).

2 - use Calendar lists

Another very promising option could be to leverage Calendar lists functionality. They have recurring events and you can create birthdays there and display them using "Current Events" standard view.

3 - use dynamic SPQuery (thanks Stuart Pegg for this idea!)

You can create the SPQuery programmatically on each page load. To avoid additional requests to database, you can consider the following scenario:

First, create two calculated columns:

1. Birthday_Month: =MONTH([Birthday])
2. Birthday_Day: =DAY([Birthday])

Then, use the following CAML query:

<Where>
  <And>
    <Eq>
      <FieldRef Name='Birthday_Month' />
      <Value Type='Number'>{0}</Value>
    </Eq>
    <Eq>
      <FieldRef Name='Birthday_Day' />
      <Value Type='Number'>{1}</Value>
    </Eq>
  </And>
</Where>

Replace {0} with the current month number (DateTime.Now.Month), and {1} with the current day number (DateTime.Now.Day).

Additionally, you can hide the calculated columns from DisplayForm page setting SPField.ShowInDisplayForm property to false.

Answer 2

Edit: Doesn't actually work; possibly due to issues between <In> and DateTimes.

Ready for some madness? This is an ungodly and unmaintainable solution, but it's worth it for the laugh:

<In>
    <FieldRef Name="Birthday" />
    <Values>
        <Value Type="DateTime" IncludeTimeValue="FALSE">
            <Today OffsetDays="-365" />
        </Value>
        <Value Type="DateTime" IncludeTimeValue="FALSE">
            <Today OffsetDays="-730" />
        </Value>
        <Value Type="DateTime" IncludeTimeValue="FALSE">
            <Today OffsetDays="-1095" />
        </Value>
        <Value Type="DateTime" IncludeTimeValue="FALSE">
            <Today OffsetDays="-1461" />
        </Value>
        ...
    </Values>
</In>

Note that this will need to take into account leap years; hence 1 will need to be subtracted from those numbers in 29 days time.

Answer 3

Try something like this:

<FieldRef Name='Birthday' /><Value Type='DateTime'><Today /></Value>