Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

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)

share|improve this question

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
    
+1 I was just thinking of a similar answer –  Stuart Pegg Jan 31 '12 at 18:08
    
Hmm..., just realized that there is no <Day /> tag in CAML :( –  Andrey Markeev Jan 31 '12 at 18:15
    
Updated my answer. –  Andrey Markeev Jan 31 '12 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. –  Stuart Pegg Jan 31 '12 at 20:22
    
@StuartPegg, you're right, thanks a million! Updated once again :) –  Andrey Markeev Jan 31 '12 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:

<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.

share|improve this answer
    
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. –  Andrey Markeev Jan 31 '12 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... –  Andrey Markeev Jan 31 '12 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. –  Stuart Pegg Jan 31 '12 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 Jan 31 '12 at 22:26
    
@Luc: Are you using 2010? 2007 Doesn't support <In> –  Stuart Pegg Feb 1 '12 at 8:03

Try something like this:

<FieldRef Name='Birthday' /><Value Type='DateTime'><Today /></Value>
share|improve this answer
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 Jan 31 '12 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. –  Andrey Markeev Jan 31 '12 at 17:52
    
Argh, silly me! Of course it wouldn't work. Read the question too quick :) –  James Love Jan 31 '12 at 18:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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