Are you getting “Error converting data type nvarchar to datetime”?

Mates – you must be thinking that why such a long halt in my Sitecore Journey? Well, I got a chance to explore new world, new people and new land of Sitecore. My responsibilities are now increased and I am working on many technologies along with my passion – Sitecore. Seems exciting? Yes, it is. Being a Sitecore programmer, you will rarely touch SQL Server. But sometimes you need to introduce external database in your Sitecore application in order to meet business requirement. Today I am going to share one simple but life saving tip of using SQL Parameters in your code. Most of us heard that saying “Everything works on developer’s machine” 🙂 But the moment code deployed to client’s server, you face many surprises. One of such surprise is culture difference between your sever and client’s server – technically speaking “Language and DateTime Settings” difference. I have faced similar situation where my code was working fine on local server and not on client’s machine. It was showing me below error.

Below code was working fine on my local machine and not working on client’s machine. After troubleshooting I found below highlighted line was culprit.

Then I found that it’s always best practice to pass datatype in SQL Parameters when you are dealing with of “DateTime” field. So I rewrote above highlighted line as shown below and it fixed the issue.

    SqlParameter paramGMDate = new SqlParameter("@MatchDate", SqlDbType.DateTime);      paramGMDate.Value = matchStatsObject.GameDate;      cmd.Parameters.Add(paramGMDate); 

That’s it. If you follow this tip then it will save your life and will not face any surprises after shifting your code to different environment. Thanks for reading, stay tuned for more such tips and tricks!!!