What should we do first : Try to find the solution or try to find the Problem

Had I been asked this question a few years ago , I would have said "I would search for a solution" .

Most of us do this i.e.we first try to find the solution .Sometimes we succeed but most of the times we do not.After many unsuccessful attempts I realized that the step to find the solution goes through another step first, and that is Finding the problem .Not going deep in to it .

A couple of months back one of my collegue came to me with a problem " There is a job that fails on every Monday" .This job takes some values from somewhere and inserts it in SQL Server tables.The error was :
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

Earlier ,my collegue explained the client that this is not a SQL issue and suggested the poor client to touch base with the application team .But the client was not a fool .He said that there is some problem in SQL Server and he don't want to go to DEV without proof.He is not a techie though.

What Should I do , Google it or BING it :-) .we did not do that .
you can see this message in sysmessages . [select * from sys.messages where message_id=241]

We decided to reproduce the issue and with in 15 mins , we proved that the format in which Date is entered at the application level should be incorrect and datetime datatype is not recognizing it .

Repro of the issue

Repro 1
declare @date datetime ,@string char(100)
select @date =getdate()
set @string =@date

Command(s) completed successfully.

Repro 2
declare @date datetime ,@string char(100)
select @date ='28/07/2010'
set @string =@date

Msg 242, Level 16, State 3, Line 2
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


So we are near as this error is almost similar to 241 , but not the same.

Repro 3
declare @date datetime ,@string char(100)
select @date ='2010/07/28'
set @string =@date

Command(s) completed successfully.

Repro 4
declare @date datetime ,@string char(100)
select @date =NULL <-- assuming someone might be putting NULL in date and since NULL can be anything, it might not be a CHAR and we will get the error.
set @string =@date
select @string
Command(s) completed successfully. <-- We did not Repro 5

declare @date datetime ,@string char(100)
select @date ='NULL'
set @string =@date
select @string

Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.
here we got the error .

Repro 6
declare @date datetime ,@string char(100)
select @date ='NULL'

This is more clear
Msg 241, Level 16, State 1, Line 2
Conversion failed when converting datetime from character string.

So the reason could be :
The application adds single quotes to any entry .For example NULL will be converted to 'NULL' and 2010/07/28 will be converted to '2010/07/28' . In this case 'NULL' will give 241 but the date will be absolutely correct and will not throw error when inserted in the table (inside SQL Server).

Conclusion :
Always try to look for the reason behind the error/issue first rather jumping for solutions here and there .It might take time but you will learn more .

Regards
Abhay