locked
comparing Dates RRS feed

  • Question

  •  

     I have developed a custom WebPart (in vs2008) for search that i am deploying to MOSS 2007 for querying data.

    In my WebPart i am using FullTextQuery object to create my queries. I am not able to figure out how to compare dates i.e datetime field in a list in sharepoint with a date string entered in a text box. Any help will be appreciated..

     

    so far i have tried this....

     

    String.Format(@"select ScanDate from scope() where ScanDate>= {0}",ConvertToDateTime(txtDateEntered.Text);

     

    this does not work...i keep getting the error "your query is malformed"

     

    Thanks. 

    Thursday, May 29, 2008 3:06 PM

Answers

  • The date literal must be surrounded by single quotes and formatted in YYYY-MM-DD format.

     

    See the following example:

     

    SELECT Title,FileExtension,ContentType,Path FROM SCOPE() WHERE  owsStartDate >= '2003-12-01')

     

    Friday, May 30, 2008 2:49 PM
  • Try

     

    SPContext.Current.Site.Url 

     

    or

     

    SPContext.Current.Web.Url

    Saturday, May 31, 2008 7:44 PM
  • Either will work if you delcare them correctly using aliases. For example,

     

    using SharePointFTS = Microsoft.SharePoint.Search.Query;

    using MossFTS = Microsoft.Office.Server.Search.Query;

     

     

    SharePointFTS.FullTextSQLQuery query1  = new SharePointFTS.FullTextSQLQuery(site);

    MossFTS.FullTextSQLQuery query2 = new MossFTS.FullTextSQLQuery(site);

     

     

     

    Sunday, June 1, 2008 8:47 PM
  • Managed properties are not interchangable across WSS and MOSS. So if you have defined a custom managed property you will not be able to use it via the Microsoft.SharePoint.Search.Query since it does not support custom managed properties. It only uses out of the box managed properties which are stored in a different database.

     

    Wednesday, June 4, 2008 3:40 PM
  • The problem went away after i stopped iis , recopied my dll into the bin directory of my sharepoint web site and restarted iis.

    Thursday, June 5, 2008 1:13 PM

All replies

  • The date literal must be surrounded by single quotes and formatted in YYYY-MM-DD format.

     

    See the following example:

     

    SELECT Title,FileExtension,ContentType,Path FROM SCOPE() WHERE  owsStartDate >= '2003-12-01')

     

    Friday, May 30, 2008 2:49 PM
  • Thanks for your reply.

     

    Is there a way to programmatically find the url of the site the webpart resides. I want to dynamically change queries and search results based on the site they are residing.

     

    Thanks.

    Saturday, May 31, 2008 12:08 PM
  • Try

     

    SPContext.Current.Site.Url 

     

    or

     

    SPContext.Current.Web.Url

    Saturday, May 31, 2008 7:44 PM
  • Thank you so much.  They both work. Smile

     

    Saturday, May 31, 2008 9:37 PM
  • By the way....a general post for anyone who knows the answer or in case someone from Microsoft reads this.

     

    If you the FullTextQuery object in your webpart to search on custom fields in a list in Sharepoint it will not work if you reference the following namespace :

     

      Microsoft.Sharepoint.Search.Query

     

     

    However everything falls into place and works if you use the following namespace :

     

      Microsoft.Office.Server.Search.Query;

     

     

    Does anyone know if this is a bug or has someone had luck with both the namespace.

     

    Thanks. 

     

    Saturday, May 31, 2008 9:47 PM
  • Either will work if you delcare them correctly using aliases. For example,

     

    using SharePointFTS = Microsoft.SharePoint.Search.Query;

    using MossFTS = Microsoft.Office.Server.Search.Query;

     

     

    SharePointFTS.FullTextSQLQuery query1  = new SharePointFTS.FullTextSQLQuery(site);

    MossFTS.FullTextSQLQuery query2 = new MossFTS.FullTextSQLQuery(site);

     

     

     

    Sunday, June 1, 2008 8:47 PM
  •  

    Hi,

     

    Thanks again for your reply.

     

    I tried the alias approach but i am still getting the following error ' property does not exist in the schema'. When i use MossFTS alias i am not getting any error.

     

    I did check both the namespace in Microsoft documentation and they look identical except the Microsoft.Office.Server.Search.Query namespace has a couple of more objects.

     

    Thanks again.

     

    Monday, June 2, 2008 3:24 PM
  • Managed properties are not interchangable across WSS and MOSS. So if you have defined a custom managed property you will not be able to use it via the Microsoft.SharePoint.Search.Query since it does not support custom managed properties. It only uses out of the box managed properties which are stored in a different database.

     

    Wednesday, June 4, 2008 3:40 PM
  • Thanks for the reply.  I have another question

     

    My web part works fine when i deploy it to MOSS 2007 installed in a virtual machine (in my xp box) . Now i am trying to deploy it to another test server and i keep getting the error "Cannot import this webpart". I am not deploying the dll to gac. Just copying it to the bin directory of the web site.

     

    Is this just a security issue...or something else....I am going to continue investigating it. Thanks.

     

    Wednesday, June 4, 2008 6:28 PM
  • The problem went away after i stopped iis , recopied my dll into the bin directory of my sharepoint web site and restarted iis.

    Thursday, June 5, 2008 1:13 PM
  •  

    The operator > '2003-12-01' it's work but operator >= it's not work..
    • Proposed as answer by k a u s h i Tuesday, January 13, 2009 7:47 AM
    • Unproposed as answer by Mike Walsh FIN Tuesday, January 13, 2009 12:38 PM
    Wednesday, June 11, 2008 9:14 AM
  • Hi,

    can anyone please tell me how can I search date and time field against a datetime managed property.
    Eg,.

    SELECT Title,FileExtension,ContentType,Path FROM SCOPE() WHERE  owsStartDate >= '2003-12-01 16:30:00')

    Apparently, this does not work even if I give owsStartDate > '2003-12-01 16:30:00'

    Does anyone know how to get time comparison working because I know for sure that just date works fine,
    Eg.,
    owsStartDate >= '2003-12-01'

    I want to be able to include time parameter in the FullTextQueries

    Tuesday, January 13, 2009 7:52 AM