datetime data type resulted in an out-of-range datetime value

[talibm]

Hi All,

I am a novice at sql and would appreciate it if someone can help me with a view that am trying to create us ms sql 2005. I am trying to convert a varchar field to convert it to time field using a datetime. the syntax check validates the view, but when I try to execute the view I get an error that says the conversion of a varchar data type to a datetime data type resulted in an out-of-range value. Any help at all is greatly appreciated. thanks. talib

CREATE VIEW [dbo].[PES Interview Duration]
AS
SELECT TOP (100) PERCENT dbo.Interview.uidInterview, dbo.Interview.LastName1, dbo.Interview.FirstName1, dbo.Interview.CompleteDate,
MIN(CASE dbo.Answer.QuestionUid WHEN 9940 THEN CAST(dbo.Answer.Answer AS SMALLDATETIME) END) AS [Start Time],
MIN(CASE dbo.Answer.QuestionUid WHEN 10349 THEN CAST(dbo.Answer.Answer AS SMALLDATETIME) END) AS [End Time]
FROM dbo.Interview INNER JOIN
dbo.Answer ON dbo.Answer.InterviewUid = dbo.Interview.uidInterview
GROUP BY dbo.Interview.uidInterview, dbo.Interview.LastName1, dbo.Interview.FirstName1, dbo.Interview.CompleteDate

 

[SkipVought]

Hi,

That can happen with a string date structure of dd in the first two bytes, where mm is often assumed.

 

[talibm]

Hello Skip,

The answer.answer field that I am try to convert to datetime is a varchar data type that allows nulls. Any suggestions? thanks

 

[gmmastros]

You have data that won't convert to date.

Select * from dbo.Answer where isdate(Answer) = 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom