To fetch data based on range of dates

Question

Hello All:

I am trying to get records which are either modified or created on certain date range and i have query something like :

SELECT * FROM Table1 Where Department = 'HR' AND ( (CreatedDate >= '25-09-2008' AND CreatedDate <= '25-09-2009) OR (ModifiedDate >= '25-09-2008' AND ModifiedDate <= '25-09-2009))

I have some records which does not have any value for column CreatedDate.

When I run this query, I get all the records in which both CreatedDate and ModifiedDate are within that range, but I wanted all records which has either CreatedDate or ModifiedDate fall in that range.

If I search for records only on ModifiedDate for the above date range, I will get 200 records
If I search for records only on CreatedDate for the above date range, I will get 130 records

But when I run the query as mentioned above in which i am fetching based on Modified and Created dates, I was hoping to get atleast 200 records or more but i only get 130 records.

Even if there is no CreatedDate value associated to a record, why is not being fetched based on modified date. There is an 'OR' clause.

I hope any one can help me out here

THanks

---

SharePoint Developer

Answer 1

At first glance, I dont see anything wrong with the query, however, here is how I would troubleshoot this type of problems.

Since the query on Modifieddate gives me 200 records, I would expect a query that runs on both conditions (with an OR) should atleast return 200 rows. In your case, it is returning only 130 rows and that shows a problem.

Let us do the following (i am trying to make the debugging process as simple as I could)

1. Run the query with Modified date filter and store the output to a temp table 1
2. Run the query with Created date and store the output into another temp table 2
3. Run a query on Temp Table 1 for records that are not in Temp Table 2. This will give you at least 70 records. 
4. Take one record and look at the values. Usually, this will right away tell you why those records are not fetched when you run both filters together. Some times it could be null values or could be something else.
5. If you could not figure out what is wrong, then take note of the Primary Key of that record (the record that is missing when you run both queries together).
6. Run the following part of the query "SELECT * FROM Table1 Where Department = 'HR' AND PrimaryKeyCol = PkeyValue. Make sure that this query returns the row we examined at #4. 
7. Now, keep adding the rest of the filters only one at a time. After adding each filter clause (you have 4 clauses in your query), run the query and make sure that it returns the row you are looking for.
8. Continue this and you will that the row disappears when a certain filter clause is added. Once you identify that, you can by yourself figure out what is the problem.
9. If you could not figure out, you can post back the data like "I have the following value in the column and when I apply this part of the filter the row disappears" and some one can help you right away.

Hope this helps.

---

Beyond Relational
SyntaxHelp.com

Answer 2

Merci Jacob:

Since I am using FullTextSQLQuery object to run the query from code behind (C#) in SharePoint, I can not test it by creating temp tables on SQL server.

But what I did was to test then when I get records with modified date within a certain date range, what are the values of CreatedDate column for those records. My query is returning CreatedDate as an empty string in some cases, when I try to convert it to date and time it throws an exception of invalid cast.

So for query like:

Select * From Table  .... AND (( CreatedDate is within this range ) OR (ModifiedDate is within this range))

All the records that has CreatedDate coming back as an empty strings are not fetched. From query, one would think that if CreatedDate condition is false but ModifiedDate condition is true, so it should bring those records too because there is an OR clause there.

I am guessing for a DateTime column if the returned value is not DateTime it get dropped ??


Thanks

---

SharePoint Developer

Answer 3

Hi,

Does the application have some conditions on it? Is it possible the application has filtered it for you?
Besides,  I think we can use a default value (xx-xx-xxxx) for the CreatedDate column.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.