Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

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'.

share|improve this question
1  
You really have a table named [@CCS_PYRUNPERIODS]? – Remus Rusanu Jul 9 '12 at 12:17
ya this is a table created in sap b1 – Sivajith Jul 9 '12 at 13:49
FYI I'm closing at Too Localized since the root of the problem is you don't actually know what your datatypes are... – JNK♦ Jul 9 '12 at 15:58
I would like to point out that your problems are primarily programming problems and do not generally (so far) require the knowledge of database administrators, per-se, but just the knowledge of programmers in general who are familiar with SQL and the various SQL variants (TSQL, PL-SQL, etc). You would do better to ask questions on Stack Overflow when your questions are basic SQL and then let them be migrated here. – jcolebrand♦ Jul 17 '12 at 1:32

closed as too localized by Aaron Bertrand, JNK♦ Jul 9 '12 at 15:56

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, see the FAQ.

2 Answers

up vote 2 down vote accepted

@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.

share|improve this answer
I think the implicit conversion problem comes from calling the procedure, e.g. EXEC dbo.foo 'Mar 1 2013 12:00AM'; - I think this can be happening and the OP being correct about their variables and data types. – Aaron Bertrand Jul 9 '12 at 12:33
(e.g. if the session language is not US English.) – Aaron Bertrand Jul 9 '12 at 12:41
thank u .its problem due to datatype mismatching... @dateforaftersixmonth is a date/time and U_PYMON is a string column is the problem – Sivajith Jul 9 '12 at 13:51
@Sivajith than why did you say @dateforaftersixmonth is a string? – Aaron Bertrand Jul 9 '12 at 13:54
i declared @dateforaftersixmonth as datetime variable and u_pymon is actually string... – Sivajith Jul 9 '12 at 13:58
up vote 1 down vote

Why are you passing a string like 'Mar 1 2013 12:00 AM'? Try using an unambiguous and non-regional format such as '20130301'. Surely you can use .Format() in your client application to prepare the string in the right way. For some background on this please read:

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

And also please stop using BETWEEN for date range queries. See this for why:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

share|improve this answer
thank you all...'Mar 1 2013 12:00 AM' is the date format getting from database table... – Sivajith Jul 9 '12 at 13:52
@Sivajith no it's not. If the column is a datetime column, that's not how it's stored, that's just how Management Studio might present it to you when you use PRINT or something along those lines. – Aaron Bertrand Jul 9 '12 at 13:53
hi, ya when i use the print statement i got that i always have trouble with the datetime format and this time i can't catch the error.. i got the answer...and how can i close this question – Sivajith Jul 9 '12 at 13:56

Not the answer you're looking for? Browse other questions tagged or ask your own question.