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 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 nullIt 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 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 nullIt 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 variabl
e
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/.../31/565991.aspx
http://tinyurl.com/f9r2
http://sqlblogcasts.com/blogs/tonyr.../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
>sql

Dateformat problem in Query Analyser

Hi Everybody,
i have a small problem ?
i have a two different servers
one is used for test purposes and the other one is used for live proposes

i have a table call Employee in both the servers and i got a filed call Attnd_Dttm

so when i open a query analyzer from the test database and type
select * from Employee where convert(datetime,Attnd_Dttm) like '13/01/2005'
i am getting the correct results

but when i am type the same SQL from the live databse
select * from Employee where convert(datetime,Attnd_Dttm) like '13/01/2005'
it give me the
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

My problem is how come this happen ,cos i checked form the live database server computer it's using a British standard date time format like dd/mm/yyyyy ?

Any Idea to solve this problem,cos i need to run the same SQL in both the servers without any problems ?

regards
suis

Try using this date format: 2005-01-13. (year, month, day, dash-separated). This format seems to work in all localizations.

-Ryan / Kardax

|||Hi Ryan
u r reply is not clear to me,
where can i give this command !

is there any command to find out the sql server date time format
regards
suis
|||

I think that you have differenet default DATEFORMAT option on your servers.

You could use SET SET DATEFORMAT 'mdy'

Or use explicit convert:

select * from Employee where convert(datetime,Attnd_Dttm,103) like '13/01/2005'

|||HI Konstantin Kosinsky
Thanks very much for your comments
u r solution is worked out,
but is there any command to check SQL server DateTime fornat ?

regards
suis

|||

suis,

I wonder if you could alter that column a make it datetime data type. If you can not change the data type of that column, I will suggest to store the value using ISO ('yyyymmdd') or ISO8601 ('yyyy-mm-ddThh:mis.mmm'). This way, SQL Server can interpret the string as a datetime no matter the language or formatdate settings.

Code Snippet

set dateformat dmy

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set dateformat mdy

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set language Spanish

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

set language English

go

select cast('2007-05-13T08:15:45.997' as datetime)

go

AMB