October 13, 2015 at 5:18 am
Hi all,
I'm a beginner with t sql and xml xquery ..
I've a little problem with a convertion.
I have a store procedure who receive an xml file.
In this store procedure I try to put all record in temporary table for some treatement ..
But I have a problem with one field ...
When I execute the code below I receive this error message :
The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.
The problem comes from the field DeleteDate from the second row of the xml file
how I can get the correct conversion for this field ?
I've look on my friend google but nothing to find a solution ...
declare @tbl table(actorPersonId int, personType int, actorType int, person int
, firstName varchar(100), surname varchar(100), phone varchar(100), email varchar(100)
, publication int, logUsr varchar(20), active bit, delDate datetime )
declare @xmlFile xml = '<ArrayOfActorPerson xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ActorPerson>
<CreationUser>System</CreationUser>
<CreationDate>1900-01-01T00:00:00</CreationDate>
<UpdateUser>System</UpdateUser>
<UpdateDate>1900-01-01T00:00:00</UpdateDate>
<LogUser>System</LogUser>
<DeleteDate>2015-10-11</DeleteDate>
<ActorPersonId>-1</ActorPersonId>
<PersonType>0</PersonType>
<ActorType>1</ActorType>
<Person>8942</Person>
<FirstName>xxxx</FirstName>
<Surname>xxxx</Surname>
<Phone />
<Email>xxxx</Email>
<Publication>682</Publication>
<Active>false</Active>
</ActorPerson>
<ActorPerson>
<CreationUser>System</CreationUser>
<CreationDate>1900-01-01T00:00:00</CreationDate>
<UpdateUser>System</UpdateUser>
<UpdateDate>1900-01-01T00:00:00</UpdateDate>
<LogUser>System</LogUser>
<DeleteDate>0001-01-01T00:00:00</DeleteDate>
<ActorPersonId>1</ActorPersonId>
<PersonType>2</PersonType>
<ActorType>5</ActorType>
<Person>8942</Person>
<FirstName>tototo</FirstName>
<Surname>totot</Surname>
<Phone />
<Email>xxxxx</Email>
<Publication>682</Publication>
<Active>true</Active>
</ActorPerson>
</ArrayOfActorPerson>'
INSERT INTO @tbl(actorPersonId, personType, actorType, person, firstName, surName, phone, email, publication, active, logUsr, delDate)
SELECT
res.Col.value('(ActorPersonId/text())[1]', 'int') as actorPersonId,
res.Col.value('(PersonType/text())[1]', 'int') as personType,
res.Col.value('(ActorType/text())[1]', 'int') as actorType,
res.Col.value('(Person/text())[1]', 'int') as person,
res.Col.value('(FirstName/text())[1]', 'varchar(100)') as firstName,
res.Col.value('(Surname/text())[1]', 'varchar(100)') as surname,
res.Col.value('(Phone/text())[1]', 'varchar(100)') as phone,
res.Col.value('(Email/text())[1]', 'varchar(100)') as email,
res.Col.value('(Publication/text())[1]', 'int') as publication,
res.Col.value('(Active/text())[1]', 'bit') as active,
res.Col.value('(CreationUser/text())[1]', 'varchar(20)') as creationUsr,
res.Col.value('(DeleteDate/text())[1]', 'datetime') as delDate
FROM @xmlFile.nodes('//ActorPerson') as res(Col)
SELECT * FROM @tbl
thanks for your time
christophe
October 13, 2015 at 9:51 am
Quick thought, change the data type from datetime to datetime2, the value is out of range for the former.
😎
October 13, 2015 at 4:19 pm
Hi Christophe,
The problem with the query you provided is related with the datetime datatype.
The datetime can store dates from January 1, 1753 ...
In the example the query tries to cast 0001-01-01T00:00:00 to datetime
<LogUser>System</LogUser>
<DeleteDate>0001-01-01T00:00:00</DeleteDate> <!--the year 0001 :) -->
<ActorPersonId>1</ActorPersonId>
To test this you can replace 0001-01-01T00:00:00 with the boundary condition e.g 1753-01-01T00:00:00 and that will work
.. or 1752-01-01T00:00:00 that will fail.
If you want to stick with the year 0001 you can use datetime2(0) instead datetime. The lower boundary for datetime2 is 0001.01.01
ps. take the datetime2 precision into consideration.
BOL:
datetime: https://msdn.microsoft.com/en-AU/library/ms187819.aspx
datetime2: https://msdn.microsoft.com/en-au/library/bb677335.aspx
D.Mincic
😀
MCTS Sql Server 2008, Database Development
October 14, 2015 at 3:51 am
Hello Eirikur Eiriksson and Dean Mincic,
thanks for your time that's exactly what i want ..and now that's work ..
I don't know why the property of this object is initialize on date 0001 compare to other field date ...
But now that's work all record are well in the data base
thanks
October 14, 2015 at 3:59 am
christophe.bernard 47659 (10/14/2015)
Hello Eirikur Eiriksson and Dean Mincic,thanks for your time that's exactly what i want ..and now that's work ..
I don't know why the property of this object is initialize on date 0001 compare to other field date ...
But now that's work all record are well in the data base
thanks
You are very welcome! and thank you for the feedback.
😎
I have the suspicion that the initialized value comes from .Net Date.MinValue, annoyingly it is often used to represent missing date values.
October 14, 2015 at 4:33 pm
christophe.bernard 47659 (10/14/2015)
Hello Eirikur Eiriksson and Dean Mincic,thanks for your time that's exactly what i want ..and now that's work ..
I don't know why the property of this object is initialize on date 0001 compare to other field date ...
But now that's work all record are well in the data base
thanks
Thanks for the feedback..
Cheers
:smooooth:
D.Mincic
😀
MCTS Sql Server 2008, Database Development
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply