Arithmetic overflow error converting expression to data type datetime

I need help in the following issue

I have created a procedure for the purpose of fetching the yesterday data from oracle db table and insert it into sql server db table 2012.

Using the following 

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp
	
	SELECT   
		lead(convert(varchar,convert(datetime,'01-JAN-1970 03:00:00',120) + [DAT_CLOSEDATE]/(24*60*60), 120),1,convert(varchar,convert(datetime,'01-JAN-1970 03:00:00',120) + [DAT_CLOSEDATE]/(24*60*60), 120)) over(partition by [TXT_TICKETNUMBER] order by [DAT_CLOSEDATE])AS  [CLOSE_DATE]
	INTO #Temp
	FROM OPENQUERY(ORACLE_DB, 'SELECT DAT_CLOSEDATE ,TXT_TICKETNUMBER   FROM SCHEME.TABLE')
	WHERE		[DAT_CLOSEDATE] = DATEADD(d,-1,GETDATE()) 
			
	SELECT * FROM #Temp

every thing is working as expected when I don’t use the WHERE
condition.

but once I use the WHERE condition the following error appears 

Msg 8115, Level 16, State 2, Line 4
Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

NOTE:

The DAT_CLOSEDATE column datatype in the oracle table is float.

I used the LEAD AND CONVERT functions in order to convert its values to be a
datetime datatype

I have tried to convert it to date datatype as following.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
    DROP TABLE #Temp
	
	SELECT   
		lead(convert(varchar,convert(date,'01-JAN-1970 03:00:00',120) + [DAT_CLOSEDATE]/(24*60*60), 120),1,convert(varchar,convert(date,'01-JAN-1970 03:00:00',120) + [DAT_CLOSEDATE]/(24*60*60), 120)) over(partition by [TXT_TICKETNUMBER] order by [DAT_CLOSEDATE])AS  [CLOSE_DATE]
	INTO #Temp
	FROM OPENQUERY(ORACLE_DB, 'SELECT DAT_CLOSEDATE ,TXT_TICKETNUMBER   FROM SCHEME.TABLE')
	WHERE		[DAT_CLOSEDATE] = DATEADD(d,-1,GETDATE()) 
			
	SELECT * FROM #Temp

But I got this error

Msg 206, Level 16, State 2, Line 4
Operand type clash: date is incompatible with float

Arithmetic overflow error converting expression to data type datetime in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

Unable to conver it.

create procedure temp(empid int, todaydate datetime)

select * from table where todaydate = lastdate;

here lastdate is INT TYPE

getting Arithmetic overflow error converting expression to data type datetime.

Answers

• Marked as answer by Simon_Hou Microsoft contingent staff Thursday, November 27, 2014 2:56 PM

• Marked as answer by Simon_Hou Microsoft contingent staff Thursday, November 27, 2014 2:56 PM

select * from table where todaydate = CAST(lastdate AS DATETIME)

Are you using Oracle database? It is not compiled on SQL Server

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

When I gave todaydate as 08/06/2014 its thros above error in sql server

Use ANSI date string format if possible. Try:

And what value does lastdate have? Using other data types than date, datetime or datetime2 for dates is bound to cause misery and pain.

If lastdate is an int, this is the number of days since 1900-01-01. So if you pass lastdate = 20141124, you will indeed get an overflow big time.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

if lastdate column is int data type, first you need to convert to char or varchar before converting to date

i think in your case, may be better choice

Saravana kumar C

Whats the date field having? Is it having exact date value or just an offset?

If its an exact date value like 20141124 then you can do like this

Please Mark This As Answer if it solved your issue
Please Mark This As Helpful if it helps to solve your issue
Visakh
—————————-
My MSDN Page
My Personal Blog
My Facebook Page

• Edited by Visakh16 MVP Monday, November 24, 2014 12:43 PM

This is pure garbage.

DATE is a temporal data type, not a numeric! Think how silly this is!! On a scale from 1 to 10, what color is your favorite letter of the alphabet? What is Christmas divided by Ramadan?

You also got the syntax wrong, an employee identifier should be a CHAR(n) data type, a table a cannot be name “table”, etc.

—CELKO— Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Источник

Arithmetic overflow error converting expression to data type datetime in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

The following query is giving me this error —

Msg 8115, Level 16, State 2, Line 5

Arithmetic overflow error converting expression to data type datetime.

@Year varchar ( 50 ), @Month varchar ( 50 )

start_date_tnx = @Year + ‘-‘ + @Month + ‘-‘ + cast ( day ( start_date_tnx ) as nvarchar ( 20 ))

For 2010 year and month 10 . only I have data. but for other years and months there is no data currently in the system.

I am not getting why its giving me the arithemetic over flow error.

Any suggestions in this area.

Answers

YYYY-MM-DD is not a safe format with datetime (it is with datetime2 and date).

This article from SQL Server MVP Tibor Karaszi will tell you all you need: http://www.karaszi.com/SQLServer/info_datetime.asp

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Links for SQL Server Books Online: SQL 2008, SQL 2005 and SQL 2000.
(Just click the link you need.)

The format set by the SET DATEFORMAT instruction will apply even if you put the year in the first place; so you will get exactly the same error by using either 2010-10-31 than you will get with 10-31-2010. The following piece of code show you that the placement of the year change nothing if you are using the separator «-» but that if you remove the separator «-«, the SET DATEFORMAT will lose its effect:

Sylvain Lafontaine, ing.
MVP — Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server (French)

I am not sure that got your problem. What type of start_date_tnx column?

DECLARE @y INT,@m INT,@d INT
SET @y=2010
SET @m=11
SET @d=1

If it does not help ,please post sample data + an expected result?

start_date_tnx = cast(rtrim(@y*10000+@m*100+@d) as datetime)

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Wrong date format. Check the language for the login or use the SET DATEFORMAT mdy instruction; see:

Sylvain Lafontaine, ing.
MVP — Access
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server (French)

start_date_tnx is of datetime datatype

and I am using sql server 2005

declare @Year varchar ( 50 ), @Month varchar ( 50 )

select * from transaction_tnx where start_date_tnx = @Year + ‘-‘ + @Month + ‘-‘ + cast ( day ( start_date_tnx ) as nvarchar ( 20 )) order by start_date_tnx

For 2010 year and month 10 . only I have data. but for other years and months there is no data currently in the system.

I am not getting why its giving me the arithemetic over flow error. When I am setting year to 2010 and month to 10 then I am not getting error. but for 2010, 11. its displaying the error.. Any suggestions in this area.

error msg : arithmetic overflow error converting expression to data type datetime

Источник

Arithmetic overflow error converting expression to data type datetime in sql

Вопрос

I need help in the following issue

I have created a procedure for the purpose of fetching the yesterday data from oracle db table and insert it into sql server db table 2012.

Using the following

every thing is working as expected when I don’t use the WHERE condition.

but once I use the WHERE condition the following error appears

The DAT_CLOSEDATE column datatype in the oracle table is float.

I used the LEAD AND CONVERT functions in order to convert its values to be a datetime datatype

I have tried to convert it to date datatype as following.

But I got this error

Все ответы

What is datatype of DAT_CLOSEDATE column? INT?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Cab you show an example of the Oracle value? You need to cast the float to date

DECLARE @myDate FLOAT
SET @myDate = 20180731
SELECT CAST(CAST(CAST(@myDate AS INT) AS VARCHAR(8)) AS DATE)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

First, LEAD has nothing to do with converting floats to datetime values. LEAD gives you the next value, as defined by the OVER clause, of the column in the result set, and it is a little odd to return only that value and not the next. This illustrates:

Now to the overflow error. First you break your back in the SELECT list to convert this float value to datetime, but then for some reason in the WHERE clause you expect SQL Server to understand this secret Oracle value on its own. You would need to decode in the same way.

Furthermore, even if we overlook that part, the WHERE clause is quite dubious. Run

What does it return? Are you really sure you only what the values that are on the millisecond (well on the 3.33 ms) 24 hours earlier?

Since you are using OPENQUERY, it is a lot better to put the WHERE clause inside OPENQUERY. Obviously then you need to use Oracle syntax and functions. (If you need help with that, you will have to ask in an Oracle forum.)

Источник

Arithmetic overflow error converting expression to data type datetime in sql

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I need help in the following issue

I have created a procedure for the purpose of fetching the yesterday data from oracle db table and insert it into sql server db table 2012.

Using the following

every thing is working as expected when I don’t use the WHERE condition.

but once I use the WHERE condition the following error appears

The DAT_CLOSEDATE column datatype in the oracle table is float.

I used the LEAD AND CONVERT functions in order to convert its values to be a datetime datatype

I have tried to convert it to date datatype as following.

But I got this error

What is datatype of DAT_CLOSEDATE column? INT?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

Cab you show an example of the Oracle value? You need to cast the float to date

DECLARE @myDate FLOAT
SET @myDate = 20180731
SELECT CAST(CAST(CAST(@myDate AS INT) AS VARCHAR(8)) AS DATE)

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

First, LEAD has nothing to do with converting floats to datetime values. LEAD gives you the next value, as defined by the OVER clause, of the column in the result set, and it is a little odd to return only that value and not the next. This illustrates:

Now to the overflow error. First you break your back in the SELECT list to convert this float value to datetime, but then for some reason in the WHERE clause you expect SQL Server to understand this secret Oracle value on its own. You would need to decode in the same way.

Furthermore, even if we overlook that part, the WHERE clause is quite dubious. Run

What does it return? Are you really sure you only what the values that are on the millisecond (well on the 3.33 ms) 24 hours earlier?

Since you are using OPENQUERY, it is a lot better to put the WHERE clause inside OPENQUERY. Obviously then you need to use Oracle syntax and functions. (If you need help with that, you will have to ask in an Oracle forum.)

Источник

[{«Product»:{«code»:»SS3JSW»,»label»:»IBM Sterling B2B Integrator»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Extensions»,»Platform»:[{«code»:»PF025″,»label»:»Platform Independent»}],»Version»:»All»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}},{«Product»:{«code»:»SS3JSW»,»label»:»IBM Sterling B2B Integrator»},»Business Unit»:{«code»:»BU059″,»label»:»IBM Software w/o TPS»},»Component»:»Adapters»,»Platform»:[{«code»:»»,»label»:»»}],»Version»:»»,»Edition»:»»,»Line of Business»:{«code»:»LOB59″,»label»:»Sustainability Software»}}]

I have such an issue. I want to select a column PDWEDT (it is numeric) and I need to select previous Saturday. The way how I usually do is with the help of declare statement

Text

DECLARE @CurrentWeekday INT = DATEPART(WEEKDAY, GETDATE())

DECLARE @LastSunday DATETIME = DATEADD(day, -1 * (( @CurrentWeekday % 7) - 1), GETDATE());

Afterwards, I have some code and later I just try to select the dates

Text

 AND  p.[PDWEDT] = @LastSunday

Not sure why but I am getting an error: «Arithmetic overflow error converting expression to data type datetime.»

So I see 2 potential ways to solve this problem.

1. Either to find another way to select previous Saturday instead of DECLARE statements (though as I understand it is the most common way). or 2. To cast or convert the field, however I am not sure how it can be done since it is numeric at the very moment. I will appreciate any ideas. Thank you. p/s I use SQL Server Management Studio.

check
Best Answer

jrp78

This person is a verified professional.

Verify your account
to enable IT peers to see that you are a professional.

ghost chili

Microsoft SQL Server Expert

• check
  36
  Best Answers
• thumb_up
  79
  Helpful Votes

Ahh, the code was getting sunday not saturday, try this.

EDIT: one correction

SQL

CONVERT(DATE, CONVERT(CHAR(8), p.PDWEDT ))  = cast(DATEADD(dd, DATEPART(DW,GETDATE())*-1, GETDATE()) as date)

1 found this helpful
thumb_up
thumb_down

View Best Answer in replies below

I don’t know if anyone has given you the Stern Lecture for using a non-datetime datatype to contain datetime data, but if not, consider it done.

That being said, and with the realization that we can’t always control the form of the data sent to us from outside our domain, here is one solution. I’ve even fixed the IsDate problem. By using date arithmetic rather than conversion (limiting CONVERT to change the numeric to integer so we can use modulo), all values between the two extremes will generate a valid date — although, if someone entered the date 20080231 which is not a valid date, the result will be 2008-02-29, which is a valid date but not necessarily the correct date (the date the user meant to enter). Since this will not result in an error, it may not be the result you want.

declare @test-2 table(

FirstDispursementDate numeric( 8, 0 )

);

Insert @test-2( FirstDispursementDate )

select 20020405 union all

select 20041222 union all

select 20001111 union all

select 20060223 union all

select 19991110 union all

select 19980425 union all

select 19991118 union all

select 20080723 union all

select 20020813 union all

select 17500723 union all -- Bogus date

select 20070231 union all -- Looks bad but converts to 2007-02-28

select 20029999 union all -- Looks bad but converts to 2010-06-09

select 20029901 union all -- Looks bad but converts to 2010-03-01

select 99991231 union all

select 0; -- Bogus date

-- All calculations in one statement

select FirstDispursementDate as AsNumeric,

DateAdd( mm, ((Convert( int, FirstDispursementDate / 10000 ) - 1900) * 12 )

+ ((Convert( int, FirstDispursementDate ) % 10000) / 100) - 1,

DateAdd( dd, (Convert( int, FirstDispursementDate ) % 100) - 1, 0 ))

as AsDatetimeValue

from @test-2

where FirstDispursementDate between 17530101 and 99991231; -- Fullproof "IsDate" function

-- The same calculations but separated into nested derived tables for purposes

-- of illustration only.

-- First convert numeric to int (x), then split int value into three values for

-- year, month and day (y) and finally manipulate to make a datetime value.

select DateAdd( mm, ((FDDYear - 1900) * 12) + FDDMonth - 1,

DateAdd( dd, FDDDay - 1, 0 )) as AsDatetimeValue

from(

select FirstDispursementDate / 10000 as FDDYear,

FirstDispursementDate % 10000 / 100 as FDDMonth,

FirstDispursementDate % 100 as FDDDay

from(

select Convert( int, FirstDispursementDate ) as FirstDispursementDate

from @test-2

where FirstDispursementDate between 17530101 and 99991231

) x

) y;

Tomm Carr
—
Version Normal Form — http://groups.google.com/group/vrdbms

This exception occurs when you set a date, which is earlier than January 1, 1753 to a datetime parameter in your SQL expression. Now, what does this «January 1, 1753» represent? This is the minimum date value that a DATETIME data type can have. Setting any date earlier to this date in your SQL expression will result in a SQL datetime overflow. Also such arithmetic overflow exceptions could occur when we set to a datetime field, a date which is later than 31st December, 9999, which is the maximum date value that a DATETIME data type can have.

In order to avoid such SQL exceptions, it is always a good practice to validate your date to ensure that your date time is never beyond the min and max limits of SQL date time.

The following sample code will help you to validate your date:

VB.NET:
Dim dtSQLMinDate As DateTime = New DateTime(1753, 1, 1)
Dim dtSQLMaxDate As DateTime = New DateTime(9999, 12, 31)
Private Function ValidateMyDate(ByVal dtMyDate As DateTime) As Boolean
    If dtMyDate < dtSQLMinDate Then
        MsgBox("Enter a valid date which is later than Jan 1, 1753")
    End If
    If dtMyDate > dtSQLMaxDate Then
        MsgBox("Enter a valid date which is earlier than Dec 31, 9999")
    End If
End Function

C#.NET:
DateTime dtSQLMinDate = new DateTime(1753, 1, 1);
DateTime dtSQLMaxDate = new DateTime(9999, 12, 31);
private bool ValidateMyDate(DateTime dtMyDate)
{
    if (dtMyDate < dtSQLMinDate) {
        Interaction.MsgBox("Enter a valid date which is later than Jan 1, 1753");
    }
    
    if (dtMyDate > dtSQLMaxDate) {
        Interaction.MsgBox("Enter a valid date which is earlier than Dec 31, 9999");
    }
}