By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,137 Members | 2,209 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,137 IT Pros & Developers. It's quick & easy.

Query runs OK in QueryAnalyzer, but fails in batch

P: n/a
A colleague of mine has a query which fails to run under SQLAgent
batch with the following error:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The
statement has been terminated. [SQLSTATE 01000] (Error 3621). The
step failed.

He can run the same query sucessfully via query analyzer (i.e. no
errors, and it does what he wants)

If I try to run the same query through Query Analyzer on my
workstation, I get a different error altogether:

Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Any idea what might be causing these differences in behaviour
depending on how and/or where the query is run from?

The (working) statement in question is:

================================================== ======================
insert into Summary_ReferrerSales
select DateCreated,
ReferrerID,
ReferrerIDCount,
PUIDCount,
ReferrerDescription,
0 as TotalOrderValue,
0 as TotalOrderLines
from vw_ReferrerPopularity

Warning: Null value is eliminated by an aggregate or other SET
operation.

(11996 row(s) affected)
================================================== ====================
And the table / view / function definitions (I take no responsibility
for the view definition!) are:

CREATE TABLE [Summary_ReferrerSales] (
[DateCreated] [datetime] NULL ,
[ReferrerID] [char] (2) NULL ,
[ReferrerIDCount] [int] NULL ,
[PUIDCount] [int] NULL ,
[ReferrerDescription] [nchar] (100) NULL ,
[TotalOrderValue] [numeric](18, 0) NULL ,
[TotalOrderLines] [numeric](18, 0) NULL
) ON [PRIMARY]
CREATE VIEW dbo.vw_ReferrerPopularity
AS
SELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) AS
ReferrerIDCount,
COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,
dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)
AS DateCreated,
dbo.LogReferrerID.Referrer AS ReferrerID,
dbo.LookupReferrerID.ReferrerDescription
FROM dbo.LogReferrerID INNER JOIN dbo.LookupReferrerID
ON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerID
WHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-01
00:00:00', 102))
GROUP BY dbo.LogReferrerID.Referrer,
dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),
dbo.LookupReferrerID.ReferrerDescription
HAVING (dbo.LogReferrerID.Referrer <> 'WS')
AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)
AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)
AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >
CONVERT(DATETIME, '2003-09-01 00:00:00', 102))
AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)
ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESC
CREATE TABLE [LogReferrerID] (
[LogReferrerID] [int] NULL ,
[Referrer] [varchar] (2) NULL ,
[DateUsed] [smalldatetime] NULL ,
[HTTPReferrer] [varchar] (1000) NULL ,
[TargetURL] [varchar] (1000) NULL ,
[QueryString] [varchar] (1000) NULL ,
[PUID] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [LookupReferrerID] (
[ReferrerID] [char] (2) NOT NULL ,
[ReferrerDescription] [varchar] (100) NOT NULL ,
CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED
(
[ReferrerID]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)
RETURNS datetime AS
BEGIN

declare @datestring char(10)

set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +
cast(datepart(mm,@RowDate) as char(2)) + '/' +
cast(datepart(yyyy,@RowDate) as char(4))

return cast(@datestring as datetime)
END
Jul 20 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
FUNCTION dbo.DateForGrouping is formatting @RowDate into an invalid date.
When I run that code passing in @rowdate = "2/10/2004" the value of
"datestring" becomes "10/2 /2004". Because the day has less than 2 digits
the resulting strng has a space in it and cannot be converted. You could use
this instead of the concatenation for better results:

SET @datestring = CONVERT(char(10), @RowDate, 103)

The day part will automatically be formatted with a leading zero when
necessary. Then @datestring will be "10/02/2004". If you want the date
returned as "02/10/2004" (mm/dd/yyyy) then use the parameter 101 instead of
103 in the CONVERT statement above.

Hope this helps.

"Philip Yale" <ph********@btopenworld.com> wrote in message
news:e9**************************@posting.google.c om...
A colleague of mine has a query which fails to run under SQLAgent
batch with the following error:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The
statement has been terminated. [SQLSTATE 01000] (Error 3621). The
step failed.

He can run the same query sucessfully via query analyzer (i.e. no
errors, and it does what he wants)

If I try to run the same query through Query Analyzer on my
workstation, I get a different error altogether:

Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Any idea what might be causing these differences in behaviour
depending on how and/or where the query is run from?

The (working) statement in question is:

================================================== ======================
insert into Summary_ReferrerSales
select DateCreated,
ReferrerID,
ReferrerIDCount,
PUIDCount,
ReferrerDescription,
0 as TotalOrderValue,
0 as TotalOrderLines
from vw_ReferrerPopularity

Warning: Null value is eliminated by an aggregate or other SET
operation.

(11996 row(s) affected)
================================================== ====================
And the table / view / function definitions (I take no responsibility
for the view definition!) are:

CREATE TABLE [Summary_ReferrerSales] (
[DateCreated] [datetime] NULL ,
[ReferrerID] [char] (2) NULL ,
[ReferrerIDCount] [int] NULL ,
[PUIDCount] [int] NULL ,
[ReferrerDescription] [nchar] (100) NULL ,
[TotalOrderValue] [numeric](18, 0) NULL ,
[TotalOrderLines] [numeric](18, 0) NULL
) ON [PRIMARY]
CREATE VIEW dbo.vw_ReferrerPopularity
AS
SELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) AS
ReferrerIDCount,
COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,
dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)
AS DateCreated,
dbo.LogReferrerID.Referrer AS ReferrerID,
dbo.LookupReferrerID.ReferrerDescription
FROM dbo.LogReferrerID INNER JOIN dbo.LookupReferrerID
ON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerID
WHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-01
00:00:00', 102))
GROUP BY dbo.LogReferrerID.Referrer,
dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),
dbo.LookupReferrerID.ReferrerDescription
HAVING (dbo.LogReferrerID.Referrer <> 'WS')
AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)
AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)
AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >
CONVERT(DATETIME, '2003-09-01 00:00:00', 102))
AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)
ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESC
CREATE TABLE [LogReferrerID] (
[LogReferrerID] [int] NULL ,
[Referrer] [varchar] (2) NULL ,
[DateUsed] [smalldatetime] NULL ,
[HTTPReferrer] [varchar] (1000) NULL ,
[TargetURL] [varchar] (1000) NULL ,
[QueryString] [varchar] (1000) NULL ,
[PUID] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [LookupReferrerID] (
[ReferrerID] [char] (2) NOT NULL ,
[ReferrerDescription] [varchar] (100) NOT NULL ,
CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED
(
[ReferrerID]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)
RETURNS datetime AS
BEGIN

declare @datestring char(10)

set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +
cast(datepart(mm,@RowDate) as char(2)) + '/' +
cast(datepart(yyyy,@RowDate) as char(4))

return cast(@datestring as datetime)
END

Jul 20 '05 #2

P: n/a
[posted and mailed, please reply in news]

Philip Yale (ph********@btopenworld.com) writes:
A colleague of mine has a query which fails to run under SQLAgent
batch with the following error:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The
statement has been terminated. [SQLSTATE 01000] (Error 3621). The
step failed.

He can run the same query sucessfully via query analyzer (i.e. no
errors, and it does what he wants)

If I try to run the same query through Query Analyzer on my
workstation, I get a different error altogether:

Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.


No, that is the same error. There is just some more extra-mumbo jumbo
in the error from SQL Agent.

The cause of the problem was correctly identified by Jason, the
function DateForGrouping.

The reason that it works sometimes, and sometimes not, is likely to
depend on language setting. I see that you are from the UK, so for you
the meaning of 10/2/2004 is clear, but SQL Server is manufactured in
a country that has a very funny way of writing dates, so with the
default setting SQL Server, thinks that date is something else. And
on a perfectly normal date like 20/2/2004, it chokes.

You and the account from which the job runs in SQL Agent, have us_english
as youre default langauge setting, and your colleague has British.

I suggest that you replace that UDF, by this expression:

convert(char(8), dbo.LogReferrerID.DateUsed, 112)

The format code 112 gives you a date like 20040210, which has the nice
property that this format is always interpreted correctly by SQL Server.

Also use this format for date literal. That is, change

CONVERT(DATETIME, '2003-09-01 00:00:00', 102))

to simply:

'20030901'

The delimited format is subject to dateformat settings.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #3

P: n/a
"Jason Sauer" <js****@johnsoninv.com> wrote in message news:<40***********************@nnrp.fuse.net>...
FUNCTION dbo.DateForGrouping is formatting @RowDate into an invalid date.
When I run that code passing in @rowdate = "2/10/2004" the value of
"datestring" becomes "10/2 /2004". Because the day has less than 2 digits
the resulting strng has a space in it and cannot be converted. You could use
this instead of the concatenation for better results:

SET @datestring = CONVERT(char(10), @RowDate, 103)

The day part will automatically be formatted with a leading zero when
necessary. Then @datestring will be "10/02/2004". If you want the date
returned as "02/10/2004" (mm/dd/yyyy) then use the parameter 101 instead of
103 in the CONVERT statement above.

Hope this helps.

"Philip Yale" <ph********@btopenworld.com> wrote in message
news:e9**************************@posting.google.c om...
A colleague of mine has a query which fails to run under SQLAgent
batch with the following error:

The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value. [SQLSTATE 22007] (Error 242) The
statement has been terminated. [SQLSTATE 01000] (Error 3621). The
step failed.

He can run the same query sucessfully via query analyzer (i.e. no
errors, and it does what he wants)

If I try to run the same query through Query Analyzer on my
workstation, I get a different error altogether:

Server: Msg 242, Level 16, State 3, Procedure DateForGrouping, Line 11
The conversion of a char data type to a datetime data type resulted in
an out-of-range datetime value.

Any idea what might be causing these differences in behaviour
depending on how and/or where the query is run from?

The (working) statement in question is:

================================================== ======================
insert into Summary_ReferrerSales
select DateCreated,
ReferrerID,
ReferrerIDCount,
PUIDCount,
ReferrerDescription,
0 as TotalOrderValue,
0 as TotalOrderLines
from vw_ReferrerPopularity

Warning: Null value is eliminated by an aggregate or other SET
operation.

(11996 row(s) affected)
================================================== ====================
And the table / view / function definitions (I take no responsibility
for the view definition!) are:

CREATE TABLE [Summary_ReferrerSales] (
[DateCreated] [datetime] NULL ,
[ReferrerID] [char] (2) NULL ,
[ReferrerIDCount] [int] NULL ,
[PUIDCount] [int] NULL ,
[ReferrerDescription] [nchar] (100) NULL ,
[TotalOrderValue] [numeric](18, 0) NULL ,
[TotalOrderLines] [numeric](18, 0) NULL
) ON [PRIMARY]
CREATE VIEW dbo.vw_ReferrerPopularity
AS
SELECT TOP 100 PERCENT COUNT(dbo.LogReferrerID.Referrer) AS
ReferrerIDCount,
COUNT(DISTINCT dbo.LogReferrerID.PUID) AS PUIDCount,
dbo.DateForGrouping(dbo.LogReferrerID.DateUsed)
AS DateCreated,
dbo.LogReferrerID.Referrer AS ReferrerID,
dbo.LookupReferrerID.ReferrerDescription
FROM dbo.LogReferrerID INNER JOIN dbo.LookupReferrerID
ON dbo.LogReferrerID.Referrer = dbo.LookupReferrerID.ReferrerID
WHERE (dbo.LogReferrerID.DateUsed > CONVERT(DATETIME, '2003-09-01
00:00:00', 102))
GROUP BY dbo.LogReferrerID.Referrer,
dbo.DateForGrouping(dbo.LogReferrerID.DateUsed),
dbo.LookupReferrerID.ReferrerDescription
HAVING (dbo.LogReferrerID.Referrer <> 'WS')
AND (COUNT(dbo.LogReferrerID.Referrer) IS NOT NULL)
AND (dbo.LookupReferrerID.ReferrerDescription IS NOT NULL)
AND (dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) >
CONVERT(DATETIME, '2003-09-01 00:00:00', 102))
AND (COUNT(DISTINCT dbo.LogReferrerID.PUID) IS NOT NULL)
ORDER BY dbo.DateForGrouping(dbo.LogReferrerID.DateUsed) DESC
CREATE TABLE [LogReferrerID] (
[LogReferrerID] [int] NULL ,
[Referrer] [varchar] (2) NULL ,
[DateUsed] [smalldatetime] NULL ,
[HTTPReferrer] [varchar] (1000) NULL ,
[TargetURL] [varchar] (1000) NULL ,
[QueryString] [varchar] (1000) NULL ,
[PUID] [varchar] (50) NULL
) ON [PRIMARY]
CREATE TABLE [LookupReferrerID] (
[ReferrerID] [char] (2) NOT NULL ,
[ReferrerDescription] [varchar] (100) NOT NULL ,
CONSTRAINT [PK_Lookup_ReferrerID] PRIMARY KEY CLUSTERED
(
[ReferrerID]
) ON [PRIMARY]
) ON [PRIMARY]
CREATE FUNCTION dbo.DateForGrouping (@RowDate datetime)
RETURNS datetime AS
BEGIN

declare @datestring char(10)

set @datestring=cast(datepart(dd,@RowDate) as char(2)) + '/' +
cast(datepart(mm,@RowDate) as char(2)) + '/' +
cast(datepart(yyyy,@RowDate) as char(4))

return cast(@datestring as datetime)
END

Jason,

Many thanks for this - there appear to be some excellent suggestions.
I'll try them out and let you know what happens.

Cheers,

Phil
Jul 20 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.