July 15, 2009 at 12:03 am
Hi,
DECLARE @STARTDATE DATETIME
SET @STARTDATE = '20090713'
SELECT * FROM TableName
WHERE DAT_TIM >= @STARTDATE AND DAT_TIM < @STARTDATE + 1[/u]
When i execute this statement in QA, it works fine. If i put the same code in SP, generating this error. The column DAT_TIM is datetime column in the table. The DAT_TIM column has index on it.
[quote-0]Arithmetic overflow error converting expression to data type datetime.[/quote-0]
The following statement runs forever without any results 🙁
SELECT * FROM TableName
WHERE CONVERT(char(8),DAT_TIM,112) = @STARTDATE
Windows 2003, SQL Server 2005 SP3
Please guide me.
July 15, 2009 at 1:05 am
Are you sure that the you got the correct SQL Statement that causes this error? Could it be that another statement is causing the error?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2009 at 1:50 am
Thanks Adi.
Try the sample code. it generates the same error
CREATE TABLE DBO.TEST (DAT_TIM DATETIME NOT NULL)
GO
INSERT TEST
SELECT GETDATE() UNION ALL
SELECT GETDATE()-1 UNION ALL
SELECT GETDATE()-2 UNION ALL
SELECT GETDATE()-3 UNION ALL
SELECT GETDATE()-4 UNION ALL
SELECT GETDATE()-5 UNION ALL
SELECT GETDATE()-6 UNION ALL
SELECT GETDATE()-7 UNION ALL
SELECT GETDATE()-8 UNION ALL
SELECT GETDATE()-9 UNION ALL
SELECT GETDATE()-10 UNION ALL
SELECT GETDATE()-11
SELECT * FROM TEST
CREATE PROC USP_TEST
@STARTDATE CHAR(8)
AS
SET NOCOUNT ON
SELECT * FROM TEST
WHERE DAT_TIM >= @STARTDATE AND DAT_TIM < @STARTDATE + 1
GO
EXEC USP_TEST '20090712'
DROP TABLE TEST
July 15, 2009 at 2:10 am
I also agreed. This statement should work fine.
May be problem is there in some other statement.
July 15, 2009 at 2:17 am
jymoorthy (7/15/2009)
CREATE PROC USP_TEST@STARTDATE CHAR(8)
Problem is with Parameter data-type. If you change the parameter from CHAR(8) to datetime, it should work fine.
When I run below query, it gives correct result:
DECLARE @STARTDATE DATETIME
SET @STARTDATE = '20090712'
SELECT * FROM TEST
WHERE DAT_TIM >= @STARTDATE AND DAT_TIM < @STARTDATE + 1
July 15, 2009 at 2:21 am
jymoorthy (7/15/2009)
The following statement runs forever without any results 🙁
SELECT * FROM TableName
WHERE CONVERT(char(8),DAT_TIM,112) = @STARTDATE
That will run forever as indexing cant be used,
SQLServer will have to scan each row and convert the DAT_TIM to a char to compare to @StartDate.
You are seeing an the error in the USP_TEST because @StartDate is a varchar(8) and you are doing @STARTDATE + 1, which makes no sense.
I cant see any reason for you initial error, reconfim data types.
July 15, 2009 at 2:21 am
Thanks Hari. It was very old code and hesitate to change anything. The change is a MUST now 🙂
Thanks again
July 15, 2009 at 2:27 am
You should change the parameter type to datetime or smalldatetime instead of char(8). When you use a character as a parameter and then add 1 to it, the server converts it to an integer, then you get the number 20090713. The server tries to convert this number to a date by adding 20090713 days to January 1th 1900, and then you get the error. If you’ll use datetime type for the parameter, the server will add 1 day to the date that you pass to it and this should solve your problems.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 15, 2009 at 10:19 pm
Thanks Adi. If i change the parameter to datetime, it works fine. But need to modify all the VB and Crystal reports code (passing the parameter). Thats why i was not interested to change anything.
When you use a character as a parameter and then add 1 to it, the server converts it to an integer, then you get the number 20090713.
Can you explain little more about this. Thanks again
July 15, 2009 at 10:21 pm
Thanks Adi. If i change the parameter to datetime, it works fine. But need to modify all the VB and Crystal reports code (passing the parameter). Thats why i was not interested to change anything.
When you use a character as a parameter and then add 1 to it, the server converts it to an integer, then you get the number 20090713.
Can you explain little more about this. Thanks again
July 16, 2009 at 1:24 am
In your code you are working with 3 data types. The first data type is the parameter’s data type - char(8) . The second data type is the columns’ data type – datetime. The third data type is integer (the number 1 from the where clause). When ever there are operations that involve more then one data type, the server has to determine how to treat each of the data types that is participating in the operation and what the will be the results’ data type. In your code you have a where clause that looks like that:
...AND DAT_TIM < @STARTDATE + 1
Those are the data types that are being used
Datetime_value <= Char_value + Integer_value
The server first tries to get the value of @startdate + 1, and since you are adding an integer to a string, the results will be an integer. Fallowing your example after this stage the expression
DAT_TIM < '20090713' + 1 turns into this expression:
DAT_TIM < 20090714
Notice that now the right side is an integer and not a string. The next step is to compare a datetime value to an integer value. The server tries to convert the integer into datetime value, but it can’t.
The best solution is to modify the parameter so it will also be datetime, but if you can’t, then there are other 2 solution. First solution is to modify the code and instead of writing:
AND DAT_TIM < @STARTDATE + 1
Write this line:
AND DAT_TIM < dateadd(dd,1,@STARTDATE)
The second solution is to convert the results of @STARTDATE+1 to char(8):
AND DAT_TIM < CAST(@STARTDATE + 1 AS CHAR(8))
[/Code]
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 16, 2009 at 6:15 am
Thanks Adi for the detailed reply. I really appreciate it. Thanks for your time.
July 16, 2009 at 8:19 am
may be this can help you
SELECT * FROM TEST
WHERE CONVERT(VARCHAR,DAT_TIM,101) >= CAST (@startdate AS DATETIME )
AND CONVERT(VARCHAR,DAT_TIM,101) < (CAST (@startdate AS DATETIME )+1)
Raj Acharya
July 16, 2009 at 1:44 pm
raj acharya (7/16/2009)
may be this can help youSELECT * FROM TEST
WHERE
CONVERT(VARCHAR,DAT_TIM,101)>= CAST (@startdate AS DATETIME )AND
CONVERT(VARCHAR,DAT_TIM,101)< (CAST (@startdate AS DATETIME )+1)
Why Convert a DATETIME to a VARCHAR and then comparing it against a date (doing so would require SQL to convert it back to a DATETIME again)??
Plus, applying a function to a column makes SQL unable to take advantage of any indexes that might exist on that column.
July 16, 2009 at 11:10 pm
.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply