The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.(#242)

I fetch data from SQL Sever 2005 through MS Access interface using SQL Pass-Through Queries (SPTQ). I have two queries with the same sql text inside (please see the code snippet) and connect to the same database, same tables; only the filter (A.CY_CYCLE Like %D-DC8-P%) is different.

When I fetch the data with filter A.CY_CYCLE Like %D-DC8-P% (newly available data in the table) MS Access does not throw any error message and all data is fetched successfully.

However when I fetch data with filter A.CY_CYCLE Like %D-DC5-P% (old data available in the table) MS Access throws error "ODBC--call failed.  [Microsoft][ODBC SQL Server Driver][SQL Server]The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.(#242)"

On the SQL server TC_EXEC_DATE field is defined as datetime whereas B.TC_EXEC_TIME field is defined as VARCHAR(15).

I want to combine these two fields and get one datetime field.

Why the same sql text works fine where as it does not work for the other???

Any answers will be much appreciated.

SELECT A.CY_CYCLE_ID AS [TESTSET_ID], A.CY_CYCLE AS [TESTSET_NAME], A.CY_OPEN_DATE AS [TESTSET_CREATION_DT], B.TC_TEST_ID AS [TESTCASE_ID],  C.TS_NAME AS [TESTCASE_NAME], C.TS_STEPS AS [TEST_STEPS],  B.TC_STATUS AS [TC_STATUS], cast((CONVERT(VARCHAR(10), B.TC_EXEC_DATE, 101)) + ' ' + (CONVERT(CHAR(8), B.TC_EXEC_TIME, 108)) as datetime)  AS [TC_EXEC_DT],  case when ISNULL(B.TC_ACTUAL_TESTER,'')='' then B.TC_TESTER_NAME else B.TC_ACTUAL_TESTER end AS [ASSOCIATE_ID],  LEFT(A.CY_CYCLE,CHARINDEX('-',A.CY_CYCLE)-1) AS MNEMONIC
 FROM TD.CYCLE AS A, TD.TESTCYCL AS B, TD.TEST AS C  WHERE a.CY_CYCLE_ID = b.TC_CYCLE_ID  AND C.TS_TEST_ID=B.TC_TEST_ID  AND A.CY_CYCLE Like '%D-DC5-P%'

Open in new window

suresh2000Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TextReportCommented:
"Why the same sql text works fine where as it does not work for the other???" you will need to examine the data that works and compare it with the data that doesn't

Does the field B.TC_EXEC_TIME always contain valid TIME's?

Please note that the line below is OK
SELECT CAST(CONVERT(VARCHAR(10), GetDate(), 101) + ' ' + CONVERT(VARCHAR(8), '13:00', 108) AS DateTime)
but the line below will error
SELECT CAST(CONVERT(VARCHAR(10), GetDate(), 101) + ' ' + CONVERT(VARCHAR(8), '13', 108) AS DateTime)
And a NULL Time will return a Null Date

Perhaps you can add a criteria to your SQL using ISDATE to validate the data.

Cheers, Andrew
SELECT A.CY_CYCLE_ID AS [TESTSET_ID]
     , A.CY_CYCLE AS [TESTSET_NAME]
     , A.CY_OPEN_DATE AS [TESTSET_CREATION_DT]
     , B.TC_TEST_ID AS [TESTCASE_ID]
     , C.TS_NAME AS [TESTCASE_NAME]
     , C.TS_STEPS AS [TEST_STEPS]
     , B.TC_STATUS AS [TC_STATUS]
     , CAST((CONVERT(VARCHAR(10), B.TC_EXEC_DATE, 101)) + ' ' + CASE WHEN ISDATE(B.TC_EXEC_TIME) = ' THEN (CONVERT(VARCHAR(8), B.TC_EXEC_TIME, 108) ELSE '' END) as datetime)  AS [TC_EXEC_DT]
     , case when ISNULL(B.TC_ACTUAL_TESTER,'')='' 
            then B.TC_TESTER_NAME 
            else B.TC_ACTUAL_TESTER 
            end AS [ASSOCIATE_ID]
     , LEFT(A.CY_CYCLE,CHARINDEX('-',A.CY_CYCLE)-1) AS MNEMONIC
FROM TD.CYCLE AS A, TD.TESTCYCL AS B, TD.TEST AS C  
WHERE a.CY_CYCLE_ID = b.TC_CYCLE_ID  
AND C.TS_TEST_ID=B.TC_TEST_ID  
AND A.CY_CYCLE Like '%D-DC5-P%'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
suresh2000Author Commented:
Hi Andrew, your suggestion to use the ISDATE criteria worked fine. The line with CAST had a small error but I corrected it. It was a nice approach. Welldone - Pramod
0
suresh2000Author Commented:
Hi Andrew, great approach. your suggestion to use ISDATE criteria worked fine. welldone - Pramod (Pramod dot Deshpandey at Gmail dot com)

Can you check my other question which has been trouble me since quite a long time http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23030221.html

Title:
Query must have at least one destination field error in MS Access 2003

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.