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)

4 Answers 4


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:

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

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.

  • +1 I was just thinking of a similar answer
    – Stu Pegg
    Commented Jan 31, 2012 at 18:08
  • Hmm..., just realized that there is no <Day /> tag in CAML :( Commented Jan 31, 2012 at 18:15
  • Updated my answer. Commented Jan 31, 2012 at 19:35
  • I think your initial answer is still valid if they're using the CAML in an SPQuery and willing to insert the current month and day.
    – Stu Pegg
    Commented Jan 31, 2012 at 20:22
  • @StuartPegg, you're right, thanks a million! Updated once again :) Commented Jan 31, 2012 at 21:04

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:

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

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.

  • Stuart, there is a mistake in MSDN documentation: Offset attribute of Today element doesn't work (this is also mentioned in comments to the MSDN article). You should replace it with OffsetDays which will work as expected. Commented Jan 31, 2012 at 20:37
  • Btw, I think the solution could be quite acceptable (about 60 values will be enough for most purposes), if not the leap years... Commented Jan 31, 2012 at 20:46
  • @omlin: Thanks, I've fixed the attributes. Yeh, the leap years kill it; you have to update the query at the end of every February. Serious technical debt.
    – Stu Pegg
    Commented Jan 31, 2012 at 21:46
  • Has anybody tried this? I'm getting "<nativehr>0x80070057</nativehr><nativestack></nativestack>" error. Either SP does not support DateTime and IN operator or I really have to sleep.
    – Luc
    Commented Jan 31, 2012 at 22:26
  • @Luc: Are you using 2010? 2007 Doesn't support <In>
    – Stu Pegg
    Commented Feb 1, 2012 at 8:03

Try something like this:

<FieldRef Name='Birthday' /><Value Type='DateTime'><Today /></Value>
  • 1
    This does not work since the birthday is i.e. 31st jan. 1980 and today is 31st jan. 2012; The two dates are not equal.
    – Luc
    Commented Jan 31, 2012 at 17:50
  • Obviously this will not work, because while the Birthday has value i.e. 31.01.1980, today's value will be 31.01.2012. Commented Jan 31, 2012 at 17:52
  • Argh, silly me! Of course it wouldn't work. Read the question too quick :)
    – James Love
    Commented Jan 31, 2012 at 18:12

Constellation Solutions has a low-cost employee anniversary and birthday solution that can be added to an on-prem or Office 365 instance of SharePoint. It uses the User Profile service to pull the current employees. You can find out more information at Employee Birthdays and Anniversaries in SharePoint

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.