How to fix (Arithmetic overflow error converting expression to data type datetime) error in SQL Server

Question

I have created a procedure for the purpose of fetching the yesterday data from an Oracle database table and insert it into a SQL Server 2012 table.

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

Everything 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.

NOTE:

The DAT_CLOSEDATE column datatype in the Oracle table is float and its datetime in sql server .

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

sample data for DAT_CLOSEDTE column in the oracle table which is FLOAT

DAT_CLOSEDATE
1531038410
1531038433
1531038438
1531038447
1531038449
1531038450
1531038506
1531038506

One of the resolution am looking for is I expect OPENQUERY syntax to be something like the following:

OPENQUERY(ORACLE_DB, 'SELECT DAT_CLOSEDATE, TXT_TICKETNUMBER   
FROM SCHEME.TABLE 
WHERE [DAT_CLOSEDATE] = trunc(sysdate)-1')

but it needs to be converted from FLOAT to DATE data type first.

Sample data is best served as DDL + DML. Please edit your question to include it, your current attempt and your desired results. For more details, read this. — Zohar Peled
– Zohar Peled, Commented Dec 31, 2018 at 9:34
Could you add some sample values of that DAT_CLOSEDATE float that you try to compare with the current date in the WHERE clause? — LukStorms
– LukStorms, Commented Dec 31, 2018 at 9:35
"The DAT_CLOSEDATE column datatype in the oracle table is float." I hate to ask, but why? If it's a date, why are you storing it as a float? Both Oracle and SQL Server and date and time datatypes. — Thom A
– Thom A ♦, Commented Dec 31, 2018 at 9:36
@Gambos You can change your oracle inner query on OPEN QUERY to change data type or create view on oracle side and after that the insert clause was simpliest :). Soo if you can put relevant sample data we're able to try to reproduce thoses error and glad to help you. — Sanpas
– Sanpas, Commented Dec 31, 2018 at 10:02

LukStorms · Accepted Answer · 2018-12-31 11:16:05Z

Here's an example snippet that uses an extra sub-query to transform the FLOAT to a DATETIME.
Which makes further calculations easier.

Since it's an example, the insert into a temp table isn't used.

declare @Table table (TXT_TICKETNUMBER VARCHAR(30), DAT_CLOSEDATE FLOAT);

insert into @Table (TXT_TICKETNUMBER, DAT_CLOSEDATE) values
('foo000042', ((CONVERT(float, DATEADD(hour,-24,GETDATE()))*86400.0)-(25567.0*86400))), 
('foo000042', ((CONVERT(float, DATEADD(hour,-23,GETDATE()))*86400.0)-(25567.0*86400))),
('bar000042', ((CONVERT(float, DATEADD(hour,-22,GETDATE()))*86400.0)-(25567.0*86400))), 
('bar000042', ((CONVERT(float, DATEADD(hour,-21,GETDATE()))*86400.0)-(25567.0*86400)));

SELECT
TICKETNUMBER, 
CLOSEDATETIME,
DAT_CLOSEDATE,
LEAD(CloseDatetime) OVER (PARTITION BY TICKETNUMBER ORDER BY CLOSEDATETIME) AS NextCLOSEDATETIME
FROM
(
    select 
     TXT_TICKETNUMBER AS TICKETNUMBER, 
     DATEADD(hour,3,CONVERT(datetime,25567.0+(DAT_CLOSEDATE/86400.0))) AS CLOSEDATETIME,
     DAT_CLOSEDATE
    from 
    (
        -- put the openquery here instead
        select TXT_TICKETNUMBER, DAT_CLOSEDATE 
        from @Table
    ) q1
) q2
WHERE CAST(CLOSEDATETIME AS DATE) = CAST(DATEADD(day,-1,GETDATE()) AS DATE)

But if you would change that Oracle query to transform that "DAT_CLOSEDATE" to a string.
For example in the YYYY-MM-DD HH24:MI:SS format.
Then that would probably makes the conversion to a T-SQL DATETIME a tad easier.

Collectives™ on Stack Overflow

How to fix (Arithmetic overflow error converting expression to data type datetime) error in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related