Error "Arithmetic overflow error converting expression to data type datetime."

Question

Error "Arithmetic overflow error converting expression to data type datetime."

Aliza Eisenstein

SSC-Addicted

Points: 408
More actions
January 12, 2005 at 4:14 pm

#85341

I am getting this error "
Arithmetic overflow error converting expression to data type datetime."
The case: I have a JAVA program that writes to a SQL DB. It is using Prepared Statements with parameters to insert the date into SQL. I have date fields that I want to pass as string parameters and SQL should put it into the date field (SQL Field Type: DateTime). When executing the statement in Query Analyzer it works fine. When running through JAVA I get the conversion exception.
I was using YYYY-MM-DD format for the strings. When I got the error I tried MM/DD/YYYY format and I still get the same error. Any clues?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ariadne SSCrazy Points: 2044 More actions · Answer 1

Would be nice if you let us see the T-SQL. I get this only on datetime and smalldatetime datatypes only when I'm not using CAST or CONVERT properly. Not saying that this is your problem, but it would help to see what the code is.

Aliza Eisenstein SSC-Addicted Points: 408 More actions · Answer 2

Here is the code. (When processing it in Query Analyzer. When using JAVA - I do the same thing through a PreparedStatement using "?" for the values and passing string parameters.

here is the code (this is a large table - sorry)

INSERT INTO IP_TRIAL_BAL_86

(eglKey, ADMITTING_NUM, A_R_ACCT, PATIENT_NAME, PRIMARY_FINAN,

SECONDARY_FINAN, ACCT_SUM_CAT, PRM_SUM_CAT, LAST_CURR_DATE,

LAST_CURR_AMNT, LAST_CURR_MISC, LATEST_DATE, LATEST_AMOUNT,

LATEST_MISC, OPENING_BALANCE, CURRENT_DEBITS, CURRENT_CREDITS,

CLOSING_BALANCE, ACCOUNT_BALANCE, ADMISSION_DATE, DISCHARGE_DATE,

PATIENT_STAT, ENTRY_DATE_DAYS, BILL_DATE_DAYS, DISCH_DATE_DAYS,

DUNN_CYCLE_STEP, DUNN_CYCLE_DATE, REVENUE_AMT, REVENUE_RMB,

REVENUE_DIEMS, REVENUE_ANC, REVENUE_LTSTY, BILLED_AMT,

BILLED_RMB, BILLED_DIEMS, BILLED_ANC, BILLED_LTSTY,

CANCELLED_AMT, CANCELLED_RMB, CANCELLED_DIEMS, CANCELLED_ANC,

CANCELLED_LTSTY, TOTAL_PAYMENTS, LAST_PAY_AMT, LAST_PAY_DATE,

DENIAL_CODE, DENIAL_DATE, CLAIM_SENT_DATE, CLM_SENT_DT_DEF,

NURSING_STATION, ROOM_LOCATION, PAT_STATUS, SIG_ERR_CATEG,

ERR_START_DT, ERR_DEPT, ERR_DT_AGING, BILL_DT_AGING,

DSCH_DT_AGING, MAIL_DT_AGING, SIG_ERR_CODE, IMPLANTABLE_AMT,

FILLER)

VALUES ('abc', '1234567', '1234', 'aliza eisen', 'blc',

'pay', 'cat', 'sum', '2005-01-01',

'123.45', 'mi', '2001-09-18', '9876.32',

'lm', '43546.23', '565', '675',

'356.12', '5657.45', '2002-12-02', '2002-12-28',

'ia', '3', '5', '6',

'step', '2004-09-18', '456.5', '565.65',

'5476.45', '5675.45', '456.23', '1343.57',

'123', '435', '546', '987',

'765', '356', '356', '345',

'4577', '457576', '99.99', '5005-01-01',

'dny', '2005-02-02', '2003-12-12', 'ref',

'nurs', 'lc', 'ps', 's',

'2000-09-09', 'dept', '2008-07-07', '2003-12-15',

'2003-09-03', '2001-09-08', 'se', '45657',

'');

Johan Bijnens SSC Guru Points: 135368 More actions · Answer 3

just my 2 ct.

I guess your LAST_PAY_DATE column is defined smalldatetime.

You want to fill it with '5005-01-01' . that is not alowed.

BOL says :

smalldatetime

Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute

I hope this helps

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 4

I've had the same problem, but not from a java app.

in my case this was down to the ODBC configuration overiding the default language setting for your user login.

check your sql login details for the default language and then check to see what your (jdbc?) driver/configuration is returning.

MVDBA

Julian Kuiters SSCertifiable Points: 6585 More actions · Answer 5

Coming from Australia, where we like to represent dates as DD/MM/YYYY, SQL Server often gives date errors. Valid dates like 28-01-2005 cause SQL Server to spit. Remember that your User Regional Settings, System Regional Settings, ODBC Configuration, and SQL Server Login settings can all cause the dates to be interpreted incorrectly.

To keep everything nice and simple, I usually either follow ISO YYYYMMDD or spell it out so windows/sql gets it right DD MON YYYY... 28 Jan 2005.

That of course works while your in English. Change to Thai and your screwed again

Julian Kuiters
juliankuiters.id.au

Mike Eilbeck SSC Veteran Points: 226 More actions · Answer 6

Hi Julian,

I was having a similar problem updating a datetime field in a trigger. Was trying to stuff the date in as dd/mm/yyyy when it should have been mm/dd/yyyy. It was your post that switched the light bulb on in my head. So thanks!

Mike

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Apart from what has been said already above, see if this provides additional help:

http://www.karaszi.com/sqlserver/info_datetime.asp

You should the explanations on "safe" and "independent" date formats.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Mike Eilbeck SSC Veteran Points: 226 More actions · Answer 8

Hi Frank,

Thanks for the link. Very interesting!

Mike