Arithmetic overflow error converting expression to data type datetime
-
I am reading a xml doc into SQl using openxml
Iam getting the error message:
Arithmetic overflow error converting expression to data type datetime
my stored procedure looks like this:
@i_XML TEXT
AS
Declare @id INT
EXEC sp_xml_preparedocument @id OUTPUT, @i_XML
INSERT INTO newads(URN,CATEGORY,TITLE,CLASSIFICATION,PGROUP,FIRSTINS,LASTINS,ADTEXT)
SELECT * FROM OPENXML(@id,'/ADS/AD')
WITH
(
URN VARCHAR(255) '@URN',
CAT VARCHAR(500) '@CAT',
PUB_NAME VARCHAR(50) 'PUBS/PUB/PUB_NAME',
CLASS VARCHAR(500) 'PUBS/PUB/CLASS',
PAGE_GROUP VARCHAR(200) 'PUBS/PUB/PAGE_GROUP',
FIRSTINS smalldatetime 'PUBS/PUB/FIRSTINS',
LASTINS smalldatetime 'PUBS/PUB/LASTINS',
ADTEXT VARCHAR(8000) 'PUBS/PUB/ADTEXT'
)
EXEC sp_xml_removedocument @id
if I change the smalldatetime data type against the fields firstins and lastins to a varchar(50) and change the datatype on the table it runs perfectly, but I want the fields to be date fileds (they come in as dd/mm/yy)
any help would be great thanks.
Natman
-
Try using SET DATEFORMAT dmy
The problem you are facing is the REGIONAL settings on your SQL server are probably MM/DD/YYYY and you are passing in DD/MM/YYYY while you know that the 31st is the DD portion SQL thinks it is MM portion.
You can find more on SET DATEFORMAT in BOL
Good Hunting!AJ Ahrens
webmaster@kritter.net -
If you're still getting problems can you provide a data sample from your XML file.
Cheers,
ll
-
I'm still getting the same problem.
My xml looks like this:
<?xml version="1.0" encoding="UTF-8"?>
<ADS>
<ADS_CREATE_TIME>2005-04-13T20:45:31</ADS_CREATE_TIME>
<AD URN="CH100" CAT="Motors">
<TRADE_STYLE>1</TRADE_STYLE>
<ORDER_VALUE_NET>232.7</ORDER_VALUE_NET>
<ORDER_VALUE_GROSS>273.42</ORDER_VALUE_GROSS>
<ORDER_CREATION_DATE>29/03/05</ORDER_CREATION_DATE>
<ORDER_CREATION_SOURCE>Lynne.Bradley</ORDER_CREATION_SOURCE>
<PUBS>
<PUB>
<PUB_NAME>BCA</PUB_NAME>
<CLASS>Car Auctions</CLASS>
<STYLE>Display</STYLE>
<PAGE_GROUP>Motors</PAGE_GROUP>
<FIRSTINS>01/04/05</FIRSTINS>
<LASTINS>15/04/05</LASTINS>
<ADTEXT>PLACE ON PAGE THREE</ADTEXT>
</PUB>
</PUBS>
<AREAS>
<AREA>NW</AREA>
</AREAS>
<ADLOCATION>
<TOWN>Frodsham</TOWN>
<COUNTY>Cheshire</COUNTY>
<PC>WA6 0ES</PC>
</ADLOCATION>
<VENDOR>
<VENDOR_NAME>Bradley, Mrs Lynne</VENDOR_NAME>
<VENDOR_TEL>01928 724370</VENDOR_TEL>
<VENDOR_FAX> </VENDOR_FAX>
<VENDOR_COMPANY_ID/>
<VENDOR_EMAIL/>
<ADDRESS>36 Hill View Ave, Helsby</ADDRESS>
<TOWN>Frodsham</TOWN>
<COUNTY>Cheshire</COUNTY>
<PC>WA6 0ES</PC>
<COUNTRY>United Kingdom</COUNTRY>
</VENDOR>
</AD>
</ADS>
thanks for the help.
I am sure I am being a dumb ass here but hope you guys can help sort it out.
-
First:
Can you verify all those dates are in the range-> January 1, 1900, through June 6, 2079
Second:
make sure that the SET DATEFORMAT dmy is used (as recomended above)
Third:
what is the default language of your setup? -> select @@language
Fourth:
you could use XPath functions to rearrange that but seams like it will overcomplicate the query unnecessarily
* Noel -
All dates are within those ranges you specified.
I have used the SET DATEFORMAT dmy as you said.
Default language us_english
still I have failure, does this stored proc look ok?
@i_XML TEXT
AS
Declare @id INT
SET DATEFORMAT dmy
EXEC sp_xml_preparedocument @id OUTPUT, @i_XML
INSERT INTO newads(URN,CATEGORY,TITLE,CLASSIFICATION,PGROUP,FIRSTINS,LASTINS,ADTEXT)
SELECT * FROM OPENXML(@id,'/ADS/AD')
WITH
(
URN VARCHAR(255) '@URN',
CAT VARCHAR(500) '@CAT',
PUB_NAME VARCHAR(50) 'PUBS/PUB/PUB_NAME',
CLASS VARCHAR(500) 'PUBS/PUB/CLASS',
PAGE_GROUP VARCHAR(200) 'PUBS/PUB/PAGE_GROUP',
FIRSTINS smalldatetime 'PUBS/PUB/FIRSTINS',
LASTINS smalldatetime 'PUBS/PUB/LASTINS',
ADTEXT VARCHAR(8000) 'PUBS/PUB/ADTEXT'
)
EXEC sp_xml_removedocument @id
-
As mentioned above, this is deffinately a date representation issue, I have tested the same here and it is purely a case of transposing the dd and mm items.
Try the following:
SELECT CAST('04/15/2005' AS datetime) -- Will work as expected
SELECT CAST('15/04/2005' AS datetime) -- Will fail with a conversion error
As mentioned above read up about SET DATEFORMAT, the reason this is failing is a simple conversion issue.
As a note to be certain that you won't hit issues with representing dates within XML it is suggested to use the ISO8601 date format which is designed for XML use, e.g. 'yyyy-mm-ddThh:mm:ss.mmm' I have tried this and it is parsed correctly with your XML file here is a link to ISO8601 info: http://www.pvv.org/~nsaa/ISO8601.html.
ll
-
Two Problems
if you add up the maximum filed length of those you specified on the query it will be > 8060 (the maximum row size)
you have to limit that varchar(8000) somehow
do you mind to specify the field list in the select statement ?
INSERT INTO newads(URN,CATEGORY,TITLE,CLASSIFICATION,PGROUP,FIRSTINS,LASTINS,ADTEXT)
SELECT
URN,CATEGORY,TITLE,CLASSIFICATION,PGROUP,FIRSTINS,LASTINS,ADTEXT
FROM OPENXML(@id,'/ADS/AD')
WITH
(
URN VARCHAR(255) '@URN',
CAT VARCHAR(500) '@CAT',
PUB_NAME VARCHAR(50) 'PUBS/PUB/PUB_NAME',
CLASS VARCHAR(500) 'PUBS/PUB/CLASS',
PAGE_GROUP VARCHAR(200) 'PUBS/PUB/PAGE_GROUP',
FIRSTINS smalldatetime 'PUBS/PUB/FIRSTINS',
LASTINS smalldatetime 'PUBS/PUB/LASTINS',
ADTEXT VARCHAR(8000) 'PUBS/PUB/ADTEXT'
hth
* Noel -
I had the same issue, spent hours trying to sort it.
In the end i sorted it.
Using the T-SQL CAST Function, CAST the Datetime fields to Varchar's and then in your WITH statment change the smalldate datatype to Varchar (50)
Like this:
SET DATEFORMAT dmy
INSERT INTO newads(URN,CATEGORY,TITLE,CLASSIFICATION,PGROUP,FIRSTINS,LASTINS,ADTEXT)
SELECT URN,CATEGORY,TITLE,CLASSIFICATION,PGROUP,CAST([FIRSTINS] AS VARCHAR (50)) [FIRSTINS],CAST([LASTINS] AS VARCHAR (50)) [LASTINS],ADTEXT
FROM OPENXML(@id,'/ADS/AD')
WITH
(
URN VARCHAR(255) '@URN',
CAT VARCHAR(500) '@CAT',
PUB_NAME VARCHAR(50) 'PUBS/PUB/PUB_NAME',
CLASS VARCHAR(500) 'PUBS/PUB/CLASS',
PAGE_GROUP VARCHAR(200) 'PUBS/PUB/PAGE_GROUP',
FIRSTINS varchar (50) 'PUBS/PUB/FIRSTINS',
LASTINS varchar (50) 'PUBS/PUB/LASTINS',
ADTEXT VARCHAR(8000) 'PUBS/PUB/ADTEXT'
Your Field in the table remain a datetime datatype.
Hope it works
-
Nothing to do with the question, but an observation all the same ...
I suggest you be very careful when posting any sort of data, XML or otherwise, on public forums. Your example appears to contain personal details, including addresses and phone numbers. I don't know the circumstances, but if this data has come from your company database, for example, then you could be in breach of confidentiality and disclosure laws.
My advice - always clean up your sample data before posting it on public websites.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply