CODEWITHSUNDEEP
sqlxmlsql-server-2008sqlxml
Suresh
Suresh

Reputation: 1169

How to handle MIN date in XML Select query

I have XML Type variable which hold XML data, i would like to retrieve data from XML, everything works fine, but when date field has MIN date value(0001-01-01T00:00:00) than its giving error. Is there any way to eliminate this error?

Error Message

The conversion of a datetimeoffset data type to a datetime data type resulted in an out-of-range value.

--Giving Error
DECLARE @tmpXML XML
SET @tmpXML ='<FormSurveyorNoteXML><FormSurveyorNoteInfo><SurveyDate>2014-05-11T00:00:00+05:30</SurveyDate><InformationDate>0001-01-01T00:00:00</InformationDate></FormSurveyorNoteInfo></FormSurveyorNoteXML>'

SELECT  
                        T1.TR1.value('InformationDate [1]', 'datetime'),
                        T1.TR1.value('SurveyDate [1]', 'datetime')                      
FROM   @tmpXML.nodes('/FormSurveyorNoteXML/FormSurveyorNoteInfo') AS T1(TR1)

--Working Perfect, when date is not MIN
DECLARE @tmpXML1 XML
SET @tmpXML1 ='<FormSurveyorNoteXML><FormSurveyorNoteInfo><SurveyDate>2014-05-11T00:00:00+05:30</SurveyDate><InformationDate>1753-01-01T00:00:00</InformationDate></FormSurveyorNoteInfo></FormSurveyorNoteXML>'

SELECT  
                        T1.TR1.value('InformationDate [1]', 'datetime'),
                        T1.TR1.value('SurveyDate [1]', 'datetime')                      
FROM   @tmpXML1.nodes('/FormSurveyorNoteXML/FormSurveyorNoteInfo') AS T1(TR1)

Let me know if you have any suggestion or input to handle MIN date issue in XML Select.

Thanks Suresh

Upvotes: 0

Views: 569

Answers (1)

Suresh
Suresh

Reputation: 1169

Answering my own question, i found solution have to use datetime2 instead of datetime, as datetime2 has larger date range.

SELECT  
                        T1.TR1.value('InformationDate [1]', 'datetime2'),
                        T1.TR1.value('SurveyDate [1]', 'datetime')                      
FROM   @tmpXML.nodes('/FormSurveyorNoteXML/FormSurveyorNoteInfo') AS T1(TR1)

Upvotes: 2

Related Questions