Problems with casting in query

Hi
I have this query
SELECT hour, rootnode, parentnode, childnode, besvaret, optaget, opgivet,
ubesvaret, total FROM (
SELECT rootnode, parentnode, childnode,
COUNT(*) AS total,
COUNT (CASE WHEN durationofconversation > 0 THEN 1 END) AS
besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'OC' THEN 1 END) AS optaget,
COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND
durationofcall >= 40 THEN 1 END) AS ubesvaret,
tcv2.dbo. fn_getdatetimeperhour(DATECALLINITIATED)
as hour
FROM V2tickets
INNER JOIN (
SELECT s.serie, a.nodename as rootnode, cs.nodename as
parentnode, c.nodename as childnode
FROM areas a, centers cs, center c, teams t, series s
WHERE (a.nodeid=1) and a.nodeid=cs.parentid and
cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
ON V2tickets.digits = H.serie
WHERE siteid=1 AND calltype='E' AND LEN(digits) = 4 and
(cast(convert(char, DATECALLINITIATED, 1) as datetime)
between '01/09/03' and '01/12/05' ) and
(cast(convert(char, DATECALLINITIATED, 8) as datetime)
between '00:00:22' and '23:59:22' )
GROUP BY tcv2.dbo. fn_getdatetimeperhour(DATECALLINITIATED)
, rootnode,
parentnode, childnode) AS D ORDER BY 3, 4
Query is using this function to group by the hour
CREATE FUNCTION fn_getdatetimeperhour (@.DATE datetime)
RETURNS smalldatetime
AS
BEGIN
declare @.mydatetime smalldatetime
set @.mydatetime = cast(DATEPART (day, @.DATE ) as varchar(2))+'-'+
cast(DATEPART (month, @.DATE ) as varchar(2))+'-'+
cast(DATEPART (year, @.DATE ) as varchar(4))+' '+
right('0' + ltrim(DATEPART ( hour, @.DATE )), 2 )+':00:00'
RETURN(@.mydatetime)
END
I get this error when executing the query.
Server: Msg 296, Level 16, State 3, Procedure fn_getdatetimeperhour, Line 6
The conversion of char data type to smalldatetime data type resulted in an
out-of-range smalldatetime value.
Does anyone have any suggestions on what I'm doing wrong? Basically the
query works if I leave out the between part from the query
(cast(convert(char, DATECALLINITIATED, 1) as datetime) between '01/09/03'
and '01/12/05' ) and
(cast(convert(char, DATECALLINITIATED, 8) as datetime)
between '00:00:22' and '23:59:22' )
regards
HenryThere exists one or more rows in your data where the value of
DATECALLINITIATED is outside the range of valid values for a smalldatetime,
(1 Jan 1900 through 6 June 2079)
But if all you want to do is group data by each hour, try using this
expression
Select Convert(VarChar(15), DATECALLINITIATED , 113) + '00'
as the group by expression, instead of the UDF. UDFs are recompiled each
time they are run, and in your query, the UDF is being run twice fpr each ro
w
in the database table. If you use the actual expression, inline in your
query, you will avoid the errror as well as substantially improve the
performance.
"Henry" wrote:

> Hi
> I have this query
> SELECT hour, rootnode, parentnode, childnode, besvaret, optaget, opgivet,
> ubesvaret, total FROM (
> SELECT rootnode, parentnode, childnode,
> COUNT(*) AS total,
> COUNT (CASE WHEN durationofconversation > 0 THEN 1 END)
AS
> besvaret,
> COUNT(CASE WHEN durationofconversation = 0 AND
> releasecause = 'OC' THEN 1 END) AS optaget,
> COUNT(CASE WHEN durationofconversation = 0 AND
> releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
> COUNT(CASE WHEN durationofconversation = 0 AND
> durationofcall >= 40 THEN 1 END) AS ubesvaret,
> tcv2.dbo. fn_getdatetimeperhour(DATECALLINITIATED)
as ho
ur
> FROM V2tickets
> INNER JOIN (
> SELECT s.serie, a.nodename as rootnode, cs.nodename a
s
> parentnode, c.nodename as childnode
> FROM areas a, centers cs, center c, teams t, series s
> WHERE (a.nodeid=1) and a.nodeid=cs.parentid and
> cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
> ON V2tickets.digits = H.serie
> WHERE siteid=1 AND calltype='E' AND LEN(digits) = 4 and
> (cast(convert(char, DATECALLINITIATED, 1) as datetime)
> between '01/09/03' and '01/12/05' ) and
> (cast(convert(char, DATECALLINITIATED, 8) as datetime)
> between '00:00:22' and '23:59:22' )
> GROUP BY tcv2.dbo. fn_getdatetimeperhour(DATECALLINITIATED)
, rootnode,
> parentnode, childnode) AS D ORDER BY 3, 4
>
> Query is using this function to group by the hour
> CREATE FUNCTION fn_getdatetimeperhour (@.DATE datetime)
> RETURNS smalldatetime
> AS
> BEGIN
> declare @.mydatetime smalldatetime
> set @.mydatetime = cast(DATEPART (day, @.DATE ) as varchar(2))+'-'+
> cast(DATEPART (month, @.DATE ) as varchar(2))+'-'+
> cast(DATEPART (year, @.DATE ) as varchar(4))+' '+
> right('0' + ltrim(DATEPART ( hour, @.DATE )), 2 )+':00:00'
> RETURN(@.mydatetime)
> END
>
> I get this error when executing the query.
> Server: Msg 296, Level 16, State 3, Procedure fn_getdatetimeperhour, Line
6
> The conversion of char data type to smalldatetime data type resulted in an
> out-of-range smalldatetime value.
>
> Does anyone have any suggestions on what I'm doing wrong? Basically the
> query works if I leave out the between part from the query
> (cast(convert(char, DATECALLINITIATED, 1) as datetime) between '01/09/03'
> and '01/12/05' ) and
> (cast(convert(char, DATECALLINITIATED, 8) as datetime)
> between '00:00:22' and '23:59:22' )
>
> regards
> Henry
>
>|||Henry,
What is
(cast(convert(char, DATECALLINITIATED, 1) as datetime) between
'01/09/03' and '01/12/05' )
supposed to do? It casts DATECALLINITIATED, which I hope is
of type datetime, into a string according to a specific format, then
back to a datetime with no format specified (so the outer cast is
depending on system and session settings). Then it compares that
datetime (if the conversion succeeds) with two strings in an ambiguous
date format. Something like this is more likely what you want:
DATECALLINITIATED between convert(smalldatetime, '01/09/03', 1)
and convert(smalldatetime, '01/12/05' ,1)
I didn't look at anything else in the query.
Steve Kass
Drew University
Henry wrote:

>Hi
>I have this query
>SELECT hour, rootnode, parentnode, childnode, besvaret, optaget, opgivet,
>ubesvaret, total FROM (
> SELECT rootnode, parentnode, childnode,
> COUNT(*) AS total,
> COUNT (CASE WHEN durationofconversation > 0 THEN 1 END) A
S
>besvaret,
> COUNT(CASE WHEN durationofconversation = 0 AND
>releasecause = 'OC' THEN 1 END) AS optaget,
> COUNT(CASE WHEN durationofconversation = 0 AND
>releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
> COUNT(CASE WHEN durationofconversation = 0 AND
>durationofcall >= 40 THEN 1 END) AS ubesvaret,
> tcv2.dbo. fn_getdatetimeperhour(DATECALLINITIATED)
as hou
r
> FROM V2tickets
> INNER JOIN (
> SELECT s.serie, a.nodename as rootnode, cs.nodename as
>parentnode, c.nodename as childnode
> FROM areas a, centers cs, center c, teams t, series s
> WHERE (a.nodeid=1) and a.nodeid=cs.parentid and
>cs.nodeid=c.parentid and c.nodeid=t.parentid and t.nodeid=s.parentid) AS H
> ON V2tickets.digits = H.serie
> WHERE siteid=1 AND calltype='E' AND LEN(digits) = 4 and
> (cast(convert(char, DATECALLINITIATED, 1) as datetime)
>between '01/09/03' and '01/12/05' ) and
> (cast(convert(char, DATECALLINITIATED, 8) as datetime)
>between '00:00:22' and '23:59:22' )
> GROUP BY tcv2.dbo. fn_getdatetimeperhour(DATECALLINITIATED)
, rootnode,
>parentnode, childnode) AS D ORDER BY 3, 4
>
>Query is using this function to group by the hour
>CREATE FUNCTION fn_getdatetimeperhour (@.DATE datetime)
>RETURNS smalldatetime
>AS
>BEGIN
> declare @.mydatetime smalldatetime
> set @.mydatetime = cast(DATEPART (day, @.DATE ) as varchar(2))+'-'+
> cast(DATEPART (month, @.DATE ) as varchar(2))+'-'+
> cast(DATEPART (year, @.DATE ) as varchar(4))+' '+
> right('0' + ltrim(DATEPART ( hour, @.DATE )), 2 )+':00:00'
> RETURN(@.mydatetime)
>END
>
>I get this error when executing the query.
>Server: Msg 296, Level 16, State 3, Procedure fn_getdatetimeperhour, Line 6
>The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value.
>
>Does anyone have any suggestions on what I'm doing wrong? Basically the
>query works if I leave out the between part from the query
>(cast(convert(char, DATECALLINITIATED, 1) as datetime) between '01/09/03'
>and '01/12/05' ) and
> (cast(convert(char, DATECALLINITIATED, 8) as datetime)
>between '00:00:22' and '23:59:22' )
>
>regards
>Henry
>
>|||> What is
> (cast(convert(char, DATECALLINITIATED, 1) as datetime) between '01/09/03'
> and '01/12/05' )
> supposed to do? It casts DATECALLINITIATED, which I hope is
> of type datetime, into a string according to a specific format, then
> back to a datetime with no format specified (so the outer cast is
> depending on system and session settings). Then it compares that
> datetime (if the conversion succeeds) with two strings in an ambiguous
> date format. Something like this is more likely what you want:
> DATECALLINITIATED between convert(smalldatetime, '01/09/03', 1)
> and convert(smalldatetime, '01/12/05' ,1)
>
Hi Steve
Thanks for your answer.
Well thats the only way I could get it to work :-o
I have tried you siggestion which doesn't give any result, not even an error
;o)
When I do a select in the query analyser on the field the format looks like
this "27-08-2003 00:02:09" which is the standard danish date and time
format, but does that mean that datetime fields are stored as strings rather
than a real (float)? Or is the presentation in the query analyser depending
on the regional settings?
I thought that your suggestion above would compare float to float values?
Excuse me, I'm still a novice in this SQL world.
regards
Henry|||> There exists one or more rows in your data where the value of
> DATECALLINITIATED is outside the range of valid values for a
> smalldatetime,
> (1 Jan 1900 through 6 June 2079)
> But if all you want to do is group data by each hour, try using this
> expression
> Select Convert(VarChar(15), DATECALLINITIATED , 113) + '00'
> as the group by expression, instead of the UDF. UDFs are recompiled each
> time they are run, and in your query, the UDF is being run twice fpr each
> row
> in the database table. If you use the actual expression, inline in your
> query, you will avoid the errror as well as substantially improve the
> performance.
Hi
Thanks for your reply.
It's seems that you are right ;o) I'll do some more testing on this.
regards
Henry|||Hi Steve
This is spooking me (we are talking about the datetime conversion and
casting)
This one here works (my old method)
SELECT hour, rootnode, parentnode, childnode, besvaret, optaget, opgivet,
ubesvaret, total FROM( SELECT rootnode, parentnode, childnode, COUNT(*) AS
total, COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' THEN 1
END) AS optaget, COUNT(CASE WHEN durationofconversation = 0 AND releasecause
= 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, COUNT(CASE WHEN
durationofconversation = 0 AND durationofcall >= 40 THEN 1 END) AS
ubesvaret, cast(convert(VarChar(15), DATECALLINITIATED , 113) + '00' as
datetime) as hour FROM V2tickets INNER JOIN (SELECT s.serie, a.nodename as
rootnode, cs.nodename as parentnode, c.nodename as childnode FROM areas a,
centers cs, center c, teams t, series s WHERE (a.nodeid=1) and
a.nodeid=cs.parentid and cs.nodeid=c.parentid and c.nodeid=t.parentid and
t.nodeid=s.parentid) AS H ON V2tickets.digits = H.serie WHERE siteid=1 AND
calltype='E' AND LEN(digits) = 4 and (cast(convert(char, DATECALLINITIATED,
1) as datetime) between '01/09/03' and '09/12/08' ) and (cast(convert(char,
DATECALLINITIATED, 8) as datetime) between '00:00:00' and '23:59:59' ) GROUP
BY cast(Convert(VarChar(15), DATECALLINITIATED , 113) + '00' as datetime),
rootnode, parentnode, childnode) AS D ORDER BY 3, 4
This one doesn't
SELECT hour, rootnode, parentnode, childnode, besvaret, optaget, opgivet,
ubesvaret, total FROM( SELECT rootnode, parentnode, childnode, COUNT(*) AS
total, COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS besvaret,
COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC' THEN 1
END) AS optaget, COUNT(CASE WHEN durationofconversation = 0 AND releasecause
= 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet, COUNT(CASE WHEN
durationofconversation = 0 AND durationofcall >= 40 THEN 1 END) AS
ubesvaret, cast(convert(VarChar(15), DATECALLINITIATED , 113) + '00' as
datetime) as hour FROM V2tickets INNER JOIN (SELECT s.serie, a.nodename as
rootnode, cs.nodename as parentnode, c.nodename as childnode FROM areas a,
centers cs, center c, teams t, series s WHERE (a.nodeid=1) and
a.nodeid=cs.parentid and cs.nodeid=c.parentid and c.nodeid=t.parentid and
t.nodeid=s.parentid) AS H ON V2tickets.digits = H.serie WHERE siteid=1 AND
calltype='E' AND LEN(digits) = 4 and DATECALLINITIATED between
convert(smalldatetime, '01/09/03', 1) and convert(smalldatetime, '09/12/08'
,1) and DATECALLINITIATED between convert(smalldatetime, '00:00:00', 8) and
convert(smalldatetime, '23:59:59' ,8) GROUP BY cast(Convert(VarChar(15),
DATECALLINITIATED , 113) + '00' as datetime), rootnode, parentnode,
childnode) AS D ORDER BY 3, 4
And neither does this
script SELECT hour, rootnode, parentnode, childnode, besvaret, optaget,
opgivet, ubesvaret, total FROM( SELECT rootnode, parentnode, childnode,
COUNT(*) AS total, COUNT(CASE WHEN durationofconversation > 0 THEN 1 END) AS
besvaret, COUNT(CASE WHEN durationofconversation = 0 AND releasecause = 'OC'
THEN 1 END) AS optaget, COUNT(CASE WHEN durationofconversation = 0 AND
releasecause = 'RL' AND durationofcall < 40 THEN 1 END) AS opgivet,
COUNT(CASE WHEN durationofconversation = 0 AND durationofcall >= 40 THEN 1
END) AS ubesvaret, cast(convert(VarChar(15), DATECALLINITIATED , 113) + '00'
as datetime) as hour FROM V2tickets INNER JOIN (SELECT s.serie, a.nodename
as rootnode, cs.nodename as parentnode, c.nodename as childnode FROM areas
a, centers cs, center c, teams t, series s WHERE (a.nodeid=1) and
a.nodeid=cs.parentid and cs.nodeid=c.parentid and c.nodeid=t.parentid and
t.nodeid=s.parentid) AS H ON V2tickets.digits = H.serie WHERE siteid=1 AND
calltype='E' AND LEN(digits) = 4 and DATECALLINITIATED between
convert(smalldatetime, '09-01-2003', 105) and convert(smalldatetime,
'12-09-2008' ,105) and DATECALLINITIATED between convert(smalldatetime,
'00:00:00', 8) and convert(smalldatetime, '23:59:59' ,8) GROUP BY
cast(Convert(VarChar(15), DATECALLINITIATED , 113) + '00' as datetime),
rootnode, parentnode, childnode) AS D ORDER BY 3, 4
Any ideas?
Henry|||On Mon, 28 Mar 2005 08:59:07 +0200, Henry wrote:
(snip)
>When I do a select in the query analyser on the field the format looks like
>this "27-08-2003 00:02:09" which is the standard danish date and time
>format, but does that mean that datetime fields are stored as strings rathe
r
>than a real (float)?
Hi Henry,
Neither. Datetime fields are stored in their own datetime format. (BOL
says that the internal representation uses 2 integers and I have no
reason to disbelieve BOL - but this actually only matters for space
calculations, not for anything else).

> Or is the presentation in the query analyser depending
>on the regional settings?
Yes. As is the presentation by any client. If you use Enterprise Manager
to check the same data, you'll see a different format. And an Access
front-end would probably yield yet another format.
The values are communicated between the client and the server in a
dedicated format that's used specifically for datetime values and the
client formats it.

> I thought that your suggestion above would compare float to float values?
No. It would compare datetime values to datetime values.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Henry,
I forgot to include this link in my previous message:
Tibor Karaszi's ultimate guide to the datetime datatypes:
http://www.karaszi.com/SQLServer/info_datetime.asp
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 27 Mar 2005 18:34:52 +0200, Henry wrote:
(snip)
>Query is using this function to group by the hour
>CREATE FUNCTION fn_getdatetimeperhour (@.DATE datetime)
>RETURNS smalldatetime
>AS
>BEGIN
> declare @.mydatetime smalldatetime
> set @.mydatetime = cast(DATEPART (day, @.DATE ) as varchar(2))+'-'+
> cast(DATEPART (month, @.DATE ) as varchar(2))+'-'+
> cast(DATEPART (year, @.DATE ) as varchar(4))+' '+
> right('0' + ltrim(DATEPART ( hour, @.DATE )), 2 )+':00:00'
> RETURN(@.mydatetime)
>END
>
>I get this error when executing the query.
>Server: Msg 296, Level 16, State 3, Procedure fn_getdatetimeperhour, Line 6
>The conversion of char data type to smalldatetime data type resulted in an
>out-of-range smalldatetime value.
Hi Henry,
The expression in the SET statement builds a string in this format:
dd-mm-yyyy hh:00:00
Since this is assigned to a smalldatetime variable, it has to be
converted. But the format you used is not "safe" - the interpretation
depends on regional settings. The error message suggests that SQL Server
"thinks" that you used the mm-dd-yyyy format.
These formats are always safe:
yyyymmdd - for date only (note: no dashes!)
yyyy-mm-ddThh:mm:ss.ttt - for date and time (note: the T is a literal
uppercase T; the dashes and colons are required and .ttt is an part for
milliseconds)
For truncating to the last hour, an easier and quicker expression would
be
SET @.mydatetime = SELECT DATEADD(hour,
DATEDIFF(hour, '20000101', @.DATE),
'20000101')

>Does anyone have any suggestions on what I'm doing wrong? Basically the
>query works if I leave out the between part from the query
>(cast(convert(char, DATECALLINITIATED, 1) as datetime) between '01/09/03'
>and '01/12/05' ) and
> (cast(convert(char, DATECALLINITIATED, 8) as datetime)
>between '00:00:22' and '23:59:22' )
Apart from the remarks Steva alrady made about this expression, you'd
also be well advise to use an unambiguous format for the datetime
constant. The suggestion made by Steve can be simplified further if you
do, since there's no need to force the correct interpretion on
conversion:
DATECALLINITIATED >= '20030901'
AND DATECALLINITIATED < '20051202'
or (depending on which interpretation of 01/09/03 and 01/12/05 you
intended):
DATECALLINITIATED >= '20030109'
AND DATECALLINITIATED < '20050113'
Another thing: I'm not sure if this expression does what you want (since
you didn't describe what you want). You'll get each datecallinitiated
from the starting date (somewhere in 2003) to the ending date (somewhere
in 2005), but ALL rows that were initiated between 38 seconds before
midnight and 22 seconds after midnight, on any day in the date range,
will be excluded!! Could it be that you actually wanted
DATECALLINITIATED >= '2003-09-01T00:00:22'
AND DATECALLINITIATED <= '2005-12-01T23:59:22'
or
DATECALLINITIATED >= '2003-01-09T00:00:22'
AND DATECALLINITIATED <= '2005-01-12T23:59:22'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Hugo

> These formats are always safe:
> yyyymmdd - for date only (note: no dashes!)
> yyyy-mm-ddThh:mm:ss.ttt - for date and time (note: the T is a literal
> uppercase T; the dashes and colons are required and .ttt is an part for
> milliseconds)
I think I'll try the one without seperators ;o)

> For truncating to the last hour, an easier and quicker expression would
> be
> SET @.mydatetime = SELECT DATEADD(hour, DATEDIFF(hour, '20000101', @.DATE),
> '20000101')
Don't really get this one

> Apart from the remarks Steva alrady made about this expression, you'd
> also be well advise to use an unambiguous format for the datetime
> constant. The suggestion made by Steve can be simplified further if you
> do, since there's no need to force the correct interpretion on
> conversion:
The problem with the out of range have been solved using Steves suggestions.

> DATECALLINITIATED >= '20030901'
> AND DATECALLINITIATED < '20051202'
> or (depending on which interpretation of 01/09/03 and 01/12/05 you
> intended):
> DATECALLINITIATED >= '20030109'
> AND DATECALLINITIATED < '20050113'
> Another thing: I'm not sure if this expression does what you want (since
> you didn't describe what you want). You'll get each datecallinitiated
> from the starting date (somewhere in 2003) to the ending date (somewhere
> in 2005), but ALL rows that were initiated between 38 seconds before
> midnight and 22 seconds after midnight, on any day in the date range,
> will be excluded!! Could it be that you actually wanted
> DATECALLINITIATED >= '2003-09-01T00:00:22'
> AND DATECALLINITIATED <= '2005-12-01T23:59:22'
> or
> DATECALLINITIATED >= '2003-01-09T00:00:22'
> AND DATECALLINITIATED <= '2005-01-12T23:59:22'
>
Yes, that's exactly what I want, since I want to "window/zoom" a periond of
the day(s) in the date range, but thanks for notifying me on this - it could
have been a mistake.