Error "Arithmetic overflow error converting expression to data type datetime."
-
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?
-
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.
-
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',
'');
-
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
-
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
-
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 -
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
-
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] -
Hi Frank,
Thanks for the link. Very interesting!
Mike
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply