Conversion failed when converting date and/or time from character string I have a problem with this query:

SELECT @dateforaftersixmonth=U_PYMON FROM [@CCS_PYRUNPERIODS] 
    WHERE @STRTEMP_DATE between U_BEGDA and U_ENDDA 


@STRTEMP_DATE is a datetime variable and @dateforaftersixmonth is a string. U_BEGDA and U_ENDDA are datetime fields and U_PYMON is varchar.

But when I execute this query I get:


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


And when I execute this query separately I can't find any error and I got the answer. Here @STRTEMP_DATE comes is 'Mar  1 2013 12:00AM'.
@STRTEMP_DATE is a datetime variable and @dateforaftersixmonth is a
  string. U_BEGDA and U_ENDDA are datetime fields and U_PYMON is
  varchar.


The error indicates that there is an implicit conversion from string to date/time. As you stated the variables and fields here, there is no implicit conversion. Since I trust the error message more than I trust you, I must conclude that you did not stated the variable and column types correctly and at least one of the following is true:


@STRTEMP_DATE is a string and U_BEGDA  or U_ENDDA is a date/time column
@STRTEMP_DATE is a date/time variable and U_ENDDA is a string column
@dateforaftersixmonth is a string and U_PYMON is a date/time columns
@dateforaftersixmonth is a date/time and U_PYMON is a string column


One of the above must be true for the conversion to occur and for the erorr message to appear. My money is on the @dateforaftersixmonth being a datetime and U_PYMON being a string (varchar or nvarchar). This also explains the aparent randomness of the error: it occurs only when you scan a range that contains values that cannot be converted.

Depending on which of the above cases happens, the solution is to appropiately modify the parameter/variable or the column to be of type datetime when storing dates/times.