out of range date error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

out of range date error

Author

Topic

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 07:42:37

select datepart (m,DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)'))
from TransactionDetails

when i execute this query i am getting an out of range date error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 07:47:23

1) The date is not in ISO format YYYYMMDD
2) The date is invalid (has Month of less than 1 or more than 12.

E 12°55'05.25"
N 56°04'39.16"

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 07:52:40

Thanks for your help but i am sorry..actually the query we are trying to run is

select DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate)[1]','varchar(200)' ) AS DT,Y.NoOfYears,convert(VARCHAR(64),DATEADD(Year,Y.NoOfYears,DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)' )),121)
from TransactionDetails AS D
JOIN DefensiveDriverDropYear AS Y
ON Y.State=D.State
where DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(50)' )!=' ' AND
D.State='NY'

This query is throwing error as out of range date.And this value we are retrieving from a xml file that is a column of another table.

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 07:54:10

Check with this...

if u'r date is valid one,
the below query returns 1 else 0

select isdate(date)

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 07:57:28

the below is returning 1 that is True for the columns having date

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 08:03:50

the below is returning 1 that is True for the columns having date..can u plz tell me what to do to rectify this so that i can add up the "noOfYears" to "date"?
Plz reply...Its urgent

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 08:10:25

select DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate)[1]','varchar(200)' ) AS DT,Y.NoOfYears,convert(VARCHAR(64),DATEADD(Year,Y.NoOfYears,DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)' )),121)
from TransactionDetails AS D
JOIN DefensiveDriverDropYear AS Y
ON Y.State=D.State
where DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(50)' )!=' ' AND
D.State='NY'

is the marked line is any column name?

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 08:25:45

no..its a node in that xml file..

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 08:34:45

Then how come u'r query validate with a node instead of column.
have you checked u'r query without 'where' part

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 08:37:57

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 08:41:50

Can u tell me in wat format
u are getting the dates

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 08:45:23

thanks a lot for ur help but i gt the ans..that is

select MAX(DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)' )) AS DT,Y.NoOfYears,DATEADD(Year,Y.NoOfYears,MAX(DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)' ))) AS "Effective Date"
from TransactionDetails AS D
JOIN DefensiveDriverDropYear AS Y
ON Y.State=D.State
where DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(50)' )!=' ' AND
D.State='NY' AND
Y.EffectiveDate<=D.

nandinip
Starting Member

7 Posts

Posted - 2008-07-23 : 08:47:50

sorry the query is

select MAX(DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)' )) AS DT,Y.NoOfYears,DATEADD(Year,Y.NoOfYears,MAX(DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(200)' ))) AS "Effective Date"
from TransactionDetails AS D
JOIN DefensiveDriverDropYear AS Y
ON Y.State=D.State
where DriverInfo.value('(//DriverInputAccidentPreventionTrainingDate) [1]','varchar(50)' )!=' ' AND
D.State='NY' AND
Y.EffectiveDate<=D.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-23 : 08:51:57

quote:
Originally posted by VGuyz

Check with this...

if u'r date is valid one,
the below query returns 1 else 0

select isdate(date)

http://sqlblogcasts.com/blogs/madhivanan/archive/2007/09/24/handle-isdate-with-care.aspx

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-23 : 09:06:07

Can u tell me the changes
which u have done...
so that, i can also know the root cause for error.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-23 : 09:19:34

The cause?
The "DriverInputAccidentPreventionTrainingDate" element has an invalid entry in the xml.

You have an invalid date in your XML accoringd to you datatype!
Please post all date texts in your XML so that we can verify.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources