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

DateTime Now Problem

Hey, basically i have a database of users and on a certain page load i want to register when they logged onto that page, so i created this snippet of code :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

but that give me the error :

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.

So I changed it to :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated = '" + DateTime.Now.ToShortDateString() + " " + DateTime.Now.ToShortTimeString() + "' WHERE UserID = '" + CurrentUserID + "'", Connection);
UpdateMyTime.ExecuteNonQuery();

as when I looked at previous times in my database there format was "27/08/2007 15:25:45" for example. but that gives me the exact same error can anyone tell me why this doesnt work? / how to get it to work? My datatype for this feild is "datetime". Thanks John

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SET LastUpdated= '" + DateTime.Now.ToString() + "' WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

OR , best way is

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

|||

Awesome this :

SqlCommand UpdateMyTime = new SqlCommand("UPDATE Users SETLastUpdated= GetDate() WHERE UserID = '" + CurrentUserID + "'",Connection);
UpdateMyTime.ExecuteNonQuery();

way did work the best, for anyone else who is having this problem, thanks loads! John

Datediff problem

Any one please tell me how to fix the view..it's producing lots of errors -

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT E.EmpNo,

IF E.ResignDate IS NOT NULL

MonthWorked = DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )

FROM Employees E

Code Snippet

Msg 156, Level 15, State 1, Procedure vw_Issues_Deducts, Line 10

Incorrect syntax near the keyword 'IF'.

Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 11

Incorrect syntax near 'MonthWorked'.

Regards

Kapalic

You have to use the CASE WHEN...

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT

E.EmpNo,

MonthWorked =

Case When E.ResignDate IS NOT NULL

DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

MonthWorked = DATEDIFF( M, E.JoinDate, GETDATE() )

END

FROM

Employees E

|||

Still geting error -

Code Snippet

Msg 102, Level 15, State 1, Procedure vw_Issues_Deducts, Line 16
Incorrect syntax near 'DATEDIFF'.

Regards

Kapalic

|||

Fixed it!

Code Snippet

ALTER VIEW [dbo].[vw_Issues_Deducts]

AS

SELECT E.EmpNo,

MonthWorked = Case When E.ResignDate IS NOT NULL THEN

DATEDIFF( M, E.JoinDate, E.ResignDate )

ELSE

DATEDIFF( M, E.JoinDate, GETDATE() )

END

FROM Employees E

Regards

Kapalic