SQL Query syntax


  • Hi,

    I am very new to enterprise search. I looking for the correct sql query for searching a specific content type that was created within a specific period. I already have a scope setup searching for the content types. I can run queries using the object model and it returns the right result.

    I would greatly appreciate any help.

    Thursday, April 24, 2008 4:38 AM


  • I thought I post answer to my own question. Hopefully someobody will find it useful.

    To use properties that are of type dateTime in the where clause, you must follow the following syntax:
    WHERE "created">=DATEADD (DAY, -5, GETGMTDATE()).

    The full query:
    SELECT Title, created, Rank, Size, Description, Write, Path FROM portal..scope() WHERE  ( ("SCOPE" = 'Transactions') ) and "created">=Dateadd(day, -5,getgmtdate())   ORDER BY "Rank" DESC. This query returns any item that were created within the last 5 days from a scope named "Transactions".

    The following explains DATEADD function in detail:

    The DATEADD function performs time and date calculations for matching properties having date types. Use the DATEADD function to obtain dates and times in a specified amount of time before the present. The following example shows the DATEADD function:

    DATEADD (DateTimeUnits, OffsetValue, DateTime)

    DateTimeUnits: specify the units of the DateTimeValue parameter. This can be one of "YEAR", "QUARTER", "MONTH", "WEEK", "DAY", "HOUR", "MINUTE", or "SECOND". This value is case-sensitive, and quotation marks are not required around the parameter.
    OffsetValue: specifies the time offset, in the units specified by the DateTimeUnits parameter. OffsetValue must be a negative integer. Positive values are not supported.
    DateTime: is a timestamp from which to calculate the offset. This cannot be a date literal. It must be either GETGMTDATE or the result of another DATEADD function.

    The DATEADD function can only be used in literal value comparisons and only on the right side of the comparison operator.

    The GETGMTDATE function returns the current date and time in Greenwich Mean Time. Remember that this value may not be the same as the local time of your Computer.

    Do not use the equals (=) comparison operator, because the internal time representation can produce rounding errors that result in unexpected matching results.

    You can use multiple DATEADD functions to combine offset units.

    The following example WHERE clause matches documents that were modified within the last five days:
    WHERE "DAV:getlastmodified" <=DATEADD (DAY, -5, GETGMTDATE())

    The following example WHERE clause matches documents that were modified within the last two days and four hours:
    WHERE "DAV:getlastmodified" <=DATEADD (DAY, -2, DATEADD (HOUR, -4, GETGMTDATE()))

    The above info was taken from the MOSS Query Tool application. This is an awesome tool that will help you test your queries before you implement them. I encourage everyone to download it.

    Thursday, May 01, 2008 6:42 PM