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)
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
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:
Then, use the following CAML query:
Additionally, you can hide the calculated columns from DisplayForm page setting SPField.ShowInDisplayForm property to
Edit: Doesn't actually work; possibly due to issues between
Ready for some madness? This is an ungodly and unmaintainable solution, but it's worth it for the laugh:
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.
Try something like this: