INTTODATETIME - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

INTTODATETIME

Author

Topic

alperozgur
Starting Member

16 Posts

Posted - 2004-10-12 : 03:17:47

HI;
I have a query as below. when i added the RED COLORED section in the query it gaves the
Server: Msg 8115, Level 16, State 2, Procedure IntToDateTime, Line 19
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

The Query :

DECLARE @Today_date int,@Musteri_Turu char(5), @Odeme_tipi char(2)
set @Today_date=(SELECT REPLACE(CONVERT(char(12), GETDATE(), 112),'/',''))
set @Musteri_Turu='M'

SELECT A.ADD_CODE AS CARI, DUE_DATE,datediff(day,(select dbo.[IntToDateTime](DUE_DATE)),(select dbo.[IntToDateTime](@Today_date))) AS GUN_FARKI,
ISNULL(AMOUNT,0) AS TUTAR, ANAL_T0 AS WHO,(datediff(day,(select dbo.[IntToDateTime](DUE_DATE)),(select dbo.[IntToDateTime](@Today_date)))* ISNULL(AMOUNT,0)) AS GEC1
INTO #TEMP
FROM SALFLDGBPT L (NOLOCK) INNER JOIN SSRFACC A (NOLOCK) ON A.ACCNT_CODE=L.ACCNT_CODE AND SUN_DB='BPT'
WHERE L.ACCNT_CODE LIKE '120%' AND SUBSTRING(A.ADD_CODE,1,1) IN (@Musteri_Turu) AND ALLOCATION IN ('5','','A') AND DUE_DATE>0
AND datediff(day,(select dbo.[IntToDateTime](DUE_DATE)),(select dbo.[IntToDateTime](@Today_date)))>0 and AMOUNT<0

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2004-10-12 : 03:33:51

quote:
Server: Msg 8115, Level 16, State 2, Procedure IntToDateTime, Line 19

What is IntToDateTime ??.. Can you please post the code! someone here would help you

- Sekar

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 03:38:33

check data type for due_date, @today_date (you declared this as int), and what data type is your parameter in intToDateTime.

--------------------
keeping it simple...

alperozgur
Starting Member

16 Posts

Posted - 2004-10-12 : 04:12:41

Here is the code. But i must say that when i remove the 'A' from the query everything works fine.

CREATE function [dbo].[IntToDateTime] (@strdate int)
returns datetime
as
begin
declare @date datetime,
@year int,
@month int,
@day int,
@datestr nvarchar(40)

select @year = (@strdate / 10000)
select @month = (@strdate - (@year * 10000)) / 100
select @day = (@strdate - (@year * 10000) - (@month * 100))

select @datestr = convert(nvarchar(4), @year) + N'-' +
convert(nvarchar(2), @month) + N'-' +
convert(nvarchar(4), @day)

select @date = convert(datetime, @datestr)

return @date
end

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 04:18:14

may be that's the problem,what's the datatype for allocation?

--------------------
keeping it simple...

alperozgur
Starting Member

16 Posts

Posted - 2004-10-12 : 04:24:09

Allocation datatype is char(1). I wonder why the Allocation field effects the due_date field. there is no relation between this two fields.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources