Query on DateTime operations

Anyway, here is the tips to resolve the problem.

Not working:

select * from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%' and CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) between '20110120' and '20110126'

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

Working:

SELECT CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) FROM from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%'


So, the loop hole is with the between operator.

Step 1: Most probably, the issue is with the data. It’s a data issue.
Step 2: Check your objects whether any changes happened on the column.
Step 3: Check for the data, any manual entry to add special symbols (Single quotes) may happen.
Step 4: Use a loop operations to identify the exact error data or use top operator to identify it manually.

All the best and sorry for my delay response.

Cheers,
Venkatesan Prabu .J
Managing Director – KaaShiv Info Tech

MVP / MVM / MCITP / MCTS / MCAD / CCNA / QAI
Microsoft SQL Server MVP / Mind Cracker MVP

Website : http://www.kaashivinfotech.com/
Blog : http://venkattechnicalblog.blogspot.com/
Profile : http://beyondrelational.com/members/jvprabhusanthi/default.aspx

Check my Microsoft Profile for more details about me : https://mvp.support.microsoft.com/default.aspx/profile/venkatesan
-------------------------------------------------------------------------------------------
Hi Venkat,

Did you get a chance to look at this email?

Regards
Alex Madaswamy
On Fri, Jan 28, 2011 at 2:31 PM, > wrote:
Hi Venkat,

I was looking through some forums to find a solution my issue and got your blog information and your email id. Can you please help me to resolve this issue?

We have a table with more than 1M records and we are retrieving records from this table in my application. This was working fine till two days back, but now its throuwing an error. See below the query and the error message we are getting.

select * from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%' and CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) between '20110120' and '20110126'

Please note:
All the fields in WHERE condition are of the data type VARCHAR
Values in the field Load_Date is in the format yymmdd

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

I tried this as well:

Removed the condition for the Load_Date from the WHERE condition and ran the query with just Rep_Code and Rep_Type, query returns the value without any issue.
Removed the condition for the Load_Date from the WHERE condition and included in the SELECT part and ran the query with just Rep_Code and Rep_Type, query runs well without any issue. Below is the modified query.
SELECT CONVERT(VARCHAR(8),CONVERT(DATETIME,(Load_Date)),112) FROM from Production_Table WHERE [Rep_Code] IN ('117','118','146','147','148','701','702','704','705','706','707','709','710','711','712','713','202') and Rep_Type LIKE '6%'
Can you please help me to find a solution for this issue?

Thanks in advance,
Alex Madaswamy