in

SharePoint Blogs

The Best Place for SharePoint-related Blogs

Mirjam's blog

Blogging about SharePoint related stuff

The "datenoyear" field in the SharePoint user profiles

 

At the moment I'm working on the new and improved version of the Macaw Birthday web part.

This web part uses the Birthday field of the user profile to query for people who will have their birthday in the next "x" days. Where you can set "x" from the web part property pane. There's a bunch of other stuff that can be set from the pane, but that's not causing problems right now.

The SQL queries you can perform against the MOSS 2007 index do not only support negative date comparisons on complete dates. This looks like (from the SDK):
...WHERE LastModifiedTime <=DATEADD (DAY, -5, GETGMTDATE())

Which means that you can only compare against dates in the past, where I want to find dates from today till "x" days from now, and I want to disregard the year, which is also not possible.

This is my next problem. Even though the class is called datenoyear it stores a year with it when you save it. And when you use the search objects to retrieve the field it returns a year with that. In my case it always returns the year 2000. If I can be sure that this is the year that is always added to a datenoyear field that would help a lot because then I would no that I need to query for a date between today in 2000 till today +"x" in 2000. But I can't find any documentation on that.

Other possible approaches are the DataColumn Expressions and the DataView RowFilter, but that will only work if I know for sure that the year will always be 2000. In that case I can query for each day separately like:
DataView.RowFilter = "Birthday = #5/13/2000# OR Birthday = #5/14/2000# OR Birthday = #5/15/2000# OR Birthday = #5/16/2000#"
if "x" would have been four.

Right now I think I'm just going to filter when writing the HTML, which means that I have a DataTable with all user profiles present on the environment, instead of just the user profiles of the people who have their birthday in the next "x" days, but I'm out of inspiration right now. If anyone has another suggestion I would appreciate that..
Oh, and if anyone can tell me for sure that the year stored for datenoyear fields is 2000 than I would be grateful for that information as well...

Published May 13 2008, 03:56 PM by Mirjam
Filed under: , ,

Comments

 

Michiel Lankamp said:

I can tell you for sure that the date isn't allways 2000, when a user updates his own profile the current year is used.

May 13, 2008 11:03 AM
 

Victor Vogelpoel said:

For a client, I took the approach you suggest in the last paragraph: load all profiles (for a certain department) and filter the wanted profiles in C#.

There is just no pretty way in SQL...

Are you enabeling output templates as well?

May 14, 2008 1:32 AM
 

Coen Hoogstede said:

I'm not sure what sharepoint allows you to do with SQL-queries, but using cast/convert in SQL allows you to convert a column of datatype datetime to an integer representation of the date in the style YYMMDD. Using a modulo of 1000 on the result eliminates the year, which allows you to query for specific dateranges

For example, your where statement to return birthdays in the next 10 days would look something like:

where

cast(convert(nvarchar, BDay.bday, 12) as int) % 1000 >= cast(convert(nvarchar, getdate(), 12) as int) % 1000

and

cast(convert(nvarchar, BDay.bday, 12) as int) % 1000 <= cast(convert(nvarchar, DATEADD (DAY, 10, getdate()), 12) as int) % 1000

Ok, given it's not a simple way and perhaps not the prettiest, it does perform the job.

May 15, 2008 2:03 AM
 

Mirjam said:

Hi Michiel,

In  my environment, if the user updates it's own profile the year is still 2000...

Regards,

Mirjam

May 19, 2008 6:57 AM
 

Mirjam said:

Hi Victor,

In real SQL there would be no problem, but it's the possibilities of the MOSS FullTextSqlQuery object that are bugging me. I also use the C# filtering now, but it's such a waste to load all profiles..

And yes, I'm also enabling output templates ;-)

Regards,

Mirjam

May 19, 2008 7:00 AM
 

heideckerj said:

I'm just trying to do a people search using the birthday property (without a year) and can not get any results.  The value in the property displays as "December 08", however when I do a search it is not found (this is after a full crawl has completed).  I even tried "08 December" just in case there was a format issue.  Any ideas?

June 11, 2008 7:54 PM
 

Escuro said:

Answer please, where i can download this web part?

If you can, let me know at ssomsikov@rambler.ru

Thanks

June 18, 2008 8:54 AM

Leave a Comment

(required )  
(optional )
(required )  
Add

About Mirjam

Mirjam van Olst works as a lead SharePoint developer at Macaw in the Netherlands. She started working for Macaw in April 2004. Before that she had her own company (Van Olst Websolutions) with which she build web applications. Mirjam has specialized in SharePoint development since June 2004. She started working with the 2007 Office system in may 2006. She has implemented several world-wide intranet environments based on SharePoint. She also gives presentations about MOSS 2007 on a regular basis and occasionally writes articles about MOSS related subjects.
Need SharePoint Training? Attend a SharePoint Bootcamp!
Posts (c) their respective authors. Everything else (c) 2007 SharePoint Experts