DateTime variable problem

Hi,
The followng snippet runs OK in SQL Query analyser when a literal date
'1/11/2006' is used for the first date comparision
When local variable @.StartDate is used instead it runs on forever (I think,
certainly an order of magnitude longer)
(I added the set dateformat dmy and switched to the numeric date format in
an effort to solve this.
Ideally I want to run with '1-Nov-2006' which for some reason runs faster
than the numeric version.)
Any ideas what I am doing wrong?
thanks
Bob
declare @.StartDate datetime
declare @.EndDate datetime
set dateformat dmy
set @.StartDate='1/11/2006'
set @.EndDate = '2/11/2006'
create Table #Temp (icp_id int)
insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
inner join metershistory mh
ON rh.id = mh.routehistory_id
inner join icps i on mh.icp_id=i.id
WHERE rh.type = 1 and i.company_id =1
and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
mh.cant_read_code is null
It will be because the optimiser is allowing for different values in the
variable. With the literal it knows to use the date index - with the variable
it is allowing for a large date range.
Look at the query plan and change the query (mayne a subquery for the date)
or give a hint or maybe include the other columns in the date index to make
it covering.
Maybe something like
FROM (select * from routehistory where read_date between @.StartDate and
@.EndDate) rh
.....
"Bob" wrote:

> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
>
|||Bob
I have a couple of questions
1) Do you have an index (probably CI would be good choice) on read_date
column?
2) What happened if you change date format to YYYYMMDD and don't use SET
DATEFORMAT
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
|||Bob,
I think that you are being encountering what has become know as 'parameter
sniffing'.
You may wish to review these articles:
Stored Procedure -Parameter Sniffing
http://blogs.msdn.com/queryoptteam/archive/2006/03/31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2006/05/17/444.aspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Bob" <bob@.nowhere.com> wrote in message
news:uRoiCglIHHA.4000@.TK2MSFTNGP06.phx.gbl...
> Hi,
> The followng snippet runs OK in SQL Query analyser when a literal date
> '1/11/2006' is used for the first date comparision
> When local variable @.StartDate is used instead it runs on forever (I
> think,
> certainly an order of magnitude longer)
> (I added the set dateformat dmy and switched to the numeric date format in
> an effort to solve this.
> Ideally I want to run with '1-Nov-2006' which for some reason runs faster
> than the numeric version.)
> Any ideas what I am doing wrong?
> thanks
> Bob
> declare @.StartDate datetime
> declare @.EndDate datetime
> set dateformat dmy
> set @.StartDate='1/11/2006'
> set @.EndDate = '2/11/2006'
> create Table #Temp (icp_id int)
> insert into #Temp select distinct mh.icp_id as icp_id FROM routehistory
> rh
> inner join metershistory mh
> ON rh.id = mh.routehistory_id
> inner join icps i on mh.icp_id=i.id
> WHERE rh.type = 1 and i.company_id =1
> and rh.read_date>= @.StartDate and rh.read_date < '2/11/2006' and
> mh.cant_read_code is null
>
|||Hi All,
Thank you for your replies.
I won't pretend I understand Parameter sniffing.
What I have done is put the query into a sproc which was my end goal anyway
and I am tuning that up.
regards
Bob

Datetime Parameter

Hi All,
I have a report with two parameters. Date1 and date2 and both are of type
datetime. when I select a date greater than 12/05/2006 the report fails
stating "the value provided for the report parameter Date2 is not valid for
its type". Now date1 has 01/05/2006 and date2 has 31/05/2006. For the
format is Australian date format and I have checked my regional settings and
they are set correctly to Australian and I have done the same on the report
server.
What is going on and how do I fix it....
Thanks
MichaelHi Michael,
Thank you for using MSDN Managed Newsgroup Support.
From you description, my understanding of this issue is: You want to
transfer the date-time parameter in (dd/mm/yyyy) format in to the report.
If I misunderstood your concern, please feel free to point it out.
By default, it's not possible to set the format of the date. However I did
a workaround for you, you can set the parameter data type to string then
use the SQL Convert() function to convert the parameter to DateTime.
The simple example of this will be like this.
Select * from orders where orderDate= CONVERT(DateTime, @.mydate, 103)
Here is the article about the CONVERT function.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
ca-co_2f3o.asp
Hope this will be helpful.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Michael/Wei
I have found the exact same problem, here's what we have discovered.
The problem is that RS swaps the month and day.
A start and end date is chosen in the datepicher controls. This is ok, but
when "view report" is activated, RS swicthes day and month and thus it is
considered a invalid date.
A clear example is when you e.g. choose 2006-01-05 in a datepicker control.
When view reports is activated, then the date has changed to 2006-05-01.
As an extra info, the problem is userspecific, we have tried to use
different users from the same computer and the result was ok with obe user
and not ok with another. The dateformat setup was 100 % simular on these
users.
The convert function is not the answer to this problem, instead I find it to
be a really annoying bug that makes the datepicker useless.
As Michael, I really would like the solution to this problem.
Forgive my english :-)
Martin
"Wei Lu" wrote:
> Hi Michael,
> Thank you for using MSDN Managed Newsgroup Support.
> From you description, my understanding of this issue is: You want to
> transfer the date-time parameter in (dd/mm/yyyy) format in to the report.
> If I misunderstood your concern, please feel free to point it out.
> By default, it's not possible to set the format of the date. However I did
> a workaround for you, you can set the parameter data type to string then
> use the SQL Convert() function to convert the parameter to DateTime.
> The simple example of this will be like this.
> Select * from orders where orderDate= CONVERT(DateTime, @.mydate, 103)
>
> Here is the article about the CONVERT function.
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_
> ca-co_2f3o.asp
> Hope this will be helpful.
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Martin,
Thank you for your post.
Unfortunately, I could not reproduct this issue on my side. When I click
the datetime picker, it could render the date correctly.
Would you please provide some additional information about the Regional
Settings?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei
My settings are as follows:
International settings/Standards and formats = "Danish"
Short dateformat = "DD-MM-YYYY"
Dateseperator = "-"
If you have an email I would be happy to send some screensdumps.
Sincerely,
Martin
"Wei Lu" wrote:
> Hi Martin,
> Thank you for your post.
> Unfortunately, I could not reproduct this issue on my side. When I click
> the datetime picker, it could render the date correctly.
> Would you please provide some additional information about the Regional
> Settings?
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Martin,
My direct email address is weilu@.ONLINE.microsoft.com (Please remove the
ONLINE before you send the email).
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Wei Lu -
I notice that you respond to certain posts "welcome to MSDN managed
newsgroup support" - how can I get this same assistance? I am an MSDN
subscriber - is there some special way to post a question to get your
attention? I am wondering how I can set a default date of TODAY in RS2005
when I have a date parameter set as datetime. I want to provide a default
value of current date. Cant seem to get it working without getting a type
incorrect when I use a function like TODAY or NOW. Thanks in advance!
"Wei Lu" wrote:
> Hi Martin,
> My direct email address is weilu@.ONLINE.microsoft.com (Please remove the
> ONLINE before you send the email).
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>

DateTime Format problem

In SQL query I have to find records which occour between two dates. I created Select query with two parameters @.date1 and @.date2 in clasue WHERE. But problem is with date format of my parameters. This format is to long. I dont wont to use time part of these parameters only date part is needed. When I put two identical dates my query doesn't find any data because both dates are eg. 2007-05-22 00:00:00. But I need data for all this day. How to correct this problem? Regards Pawel.

Use the Convert Function to convert it to a small date it will trim the time part

Where Convert(Varchar(10),@.Date1) = Convert(Varchar(10),@.Date2) ... Also you can use the third parameter in the Convert Function to get a specific format of dates i.e dd/mm/yyyy or yyyy/mm/dd etc. For a complete list

http://msdn2.microsoft.com/en-us/library/aa226054(SQL.80).aspx

Check the link

|||

If the goal is to retrieve data for a single day, the method I prefer is lower inclusion, upper exclusion. Let me explain:

declare @.dtdatetime, @.startDatedatetime, @.endDatedatetime-- assume this is the dateset @.dt ='2007-01-02 12:34:56'select-- if only a date portion is passed into the sproc -- you won't need to remove the time portion @.startDate =convert(char(10), @.dt, 120) , @.endDate =dateadd(day, 1, @.startDate)select a.*-- use column list here!from tbl awhere-- inclusive of the lower limit a.DateColumn >= @.startDate-- exclusive of the upper limitand a.DateColumn < @.endDate

Date1 = Date2 ?

How i do this :
... WHERE (id_pessoa = 1) AND (CONVERT(VARCHAR(12), Data) = CONVERT(CHAR(12), '04/02/2002'))
in format (ddmmyyyy)What is data? Is it a date?
Why do you use varchar(12)

then

and data = convert(datetime,'04/02/2002',103)

if it's the date in dd/mm/yyyy format then you don't need to convert - just use the left(data,10)

What is in format ddmmyyyy?