Fixed the issue in SQL Server 2008- Conversion failed when converting date and/or time from character string.

Today when I executed one SQL query, sometimes it worked, but sometimes it didn’t. It showed the following error:

Conversion failed when converting date and/or time from character string.

After looking into this interesting issue, I found the SQL query used

CONVERT(char(20),FieldDateName,127)

In SQL Server 2008, it is better to use CAST and Datetime2 Type. After using the following way, the error disappeared.

CAST(FieldDateName as Datetime2)