Need help in Date Range

I have no clue why my query is acting weird

If i try to run it for 1/1/2010 9/30/2010 the query takes around 1min 26 sec and  return around a million rows

and If i run for 8/1/2010 to 8/31/2010 it takes forever to it...

basically i am getting data from 5 tables and putting it in a temp table and then updating that temp table 2-3 times with some information and then displaying it.

I am stumped as to why it works fast for a longer date range and runs the  snail for a small period of time..

I am in the verge of pulling my hair and going crazy..

any help will be appreciated.

Thanks

Karen

Suppose I have a date range:

             15/09/2010  -  25/09/2010 

my user will provide another date range for example:       17/09/2010  -  22/09/2010

I want to check that user provided date range is within my date range.

Checking works if both of user provided dates are between my range. But I need to check if one of user provided date is in my date range and another is out of my date range. For example:

07/09/2010  -  22/09/2010

17/09/2010  -  27/09/2010

Here 22/09/2010 is in my date range and 17/09/2010 is also in my range.

I need to write a sql query that check user dates are in my dates.

Any help will be appritiated.

Hi All,

We have several reports where the user can specify a date range for the where clause, i.e. {[Date].[Date].[01 Jan 2008]:[Date].[Date].[ 01 Jan 2010]}. Since the date range can be very large, we are just wondering if the query processor can generate “smarter” execution plans so that the Aggregations on year and month level can be used…

Thanks in advance for your help and input.

How can I arrange the javascript to check if the date the user has typed in is within this range? -

2 weeks prior to today and 2 weeks after today

Here's what I have so far:

var prev = new Date();
            var next = new Date();
            //past date 21 day before today
            prev.setDate(prev.getDate() - 21);
            //next date 21 days after today
            next.setDate(next.getDate() + 21);
            var pastDate = (prev.getMonth() + 1) + dtCh + (prev.getDate()) + dtCh + prev.getFullYear();
            var nextDate = (next.getMonth() + 1) + dtCh + (next.getDate()) + dtCh + next.getFullYear();
            var time1 = new Date(dateSystem).getTime();
            dtStr = month + dtCh + day + dtCh + year;
            var dateuser = new Date(dtStr);
            var time2 = dateuser.getTime();
            if (time1 - time2 <= 1209600000 && time2 <= time1) {
                return true;
            } else {
                alert("Vaccines/Services date must be within this date range: " + " " + "\n" + "\n" + "                      " + pastDate + " to " + nextDate);
                return false
            }
            return true

i have the following query.

SELECT DATEADD(dd,0, DATEDIFF(dd,0,accesseddate)) AS hitdate, COUNT(hitid) AS hitsperday
FROM hits h
INNER JOIN url u
ON h.urlid = u.urlid
WHERE (accesseddate BETWEEN DATEADD(d, -6, GETDATE()) AND GETDATE() AND u.userid = @userid)
GROUP BY DATEADD(dd,0, DATEDIFF(dd,0,accesseddate))
ORDER BY hitdate DESC

but if there were no "hits" for a particular date range in the last week i only get the dates returned where there were hits. i need to get all the days returned and where there were no results, i need a zero returned.

hitdate            hitsperday

2010-10-07     2
2010-10-06     58
2010-10-04     1645
2010-10-03     1192 

what i need to return is this:     

hitdate                 hitsperday

2010-10-08     0
2010-10-07     2
2010-10-06     58
2010-10-05     0
2010-10-04     1645
2010-10-03     1192

any suggestions?

thank you very much.

Say I have a table called "Visits", and I want to create a query that counts the visits per day. 

The way I would accomplish this would be to create a query as such:

SELECT Count(VisitId) as VisitCount, Convert(varchar(32), VisitDateTime, 101) as VisitDate
FROM Visits
WHERE VisitDateTime BETWEEN '10/1/2010' AND '10/5/2010'
GROUP BY VisitDate

The problem I am trying to overcome is that if there were no visits in a given day, that day does not show in my list.  For example, if there were 3 visits on the 1st, 2 on the 2nd, none on the 3rd, and 5 on the 4th, my result set would look like:

3, 10/1/2010
2, 10/2/2010
4, 10/4/2010

How can I create my query so that I get all days in the date range listed in the result set regardless of whether the VisitCount is zero?

Thanks

hi all,

can any one help me out...why am getting "data type mis match error"

 DateTime dFromDate = dt_From.Value;
            dt_From.Value = DateTime.Parse(dFromDate.ToShortDateString());
            string From = dFromDate.ToShortDateString();

            DateTime dToDate = dt_To.Value;
            dt_To.Value = DateTime.Parse(dToDate.ToShortDateString());
            string To = dToDate.ToShortDateString();
            OleDbConnection cn = new OleDbConnection(ConfigurationSettings.AppSettings["connec"]);
            cn.Open();
            OleDbDataAdapter da = new OleDbDataAdapter("select * from Route_Expenses a where Entry_Date between '" +From+"' and '"+To+"'",cn);
            DataSet ds = new DataSet();
            da.Fill(ds);
            dataGridView1.DataSource = ds.Tables[0];

 Please..its Urgent!

I created an adhoc report model using SQL Server 2008 reporting services. I am using data from a SQL 2000 database sitting on a SQL 2000 server. I created reports with start date and end date parameters, everything worked fine.

Now my ad hoc report model uses a SQL 2000 database (this database is a replication of the database I was using)  sitting on a SQL 2008 server. When I change parameters like start date and end dates, data doesn’t refresh. It shows the same data for any date range.

Has anyone seen this problem? What could be done to fix the issue. Is there any setting on the SQL server to fix this?

Hi,

I am pulling the data into a column as XML data type and I want to query within the below XML that is returned.

I want to construct XPATH that allows me to search for a date range where Field Name = Review Date. I want records to be returned whenever the Review Date falls within a date range that I am passing from the UI

<tr2 fieldcode="23" fieldvalue="Periodic Review">

  <LF FieldName="Request Type" />

</tr2>

<tr2 fieldcode="26" fieldvalue="05/04/2010">

  <LF FieldName="Review Date" />

</tr2>

<tr2 fieldcode="27">

  <LF FieldName="Total Number of Accounts" />

</tr2>

<tr2 fieldcode="28">

  <LF FieldName="Total Number of Roles" />

</tr2>

<tr2 fieldcode="27" fieldvalue="28">

  <LF FieldName="Total Number of Accounts" />

</tr2>

<tr2 fieldcode="28" fieldvalue="252">

  <LF FieldName="Total Number of Roles" />

</tr2>