• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 11243
  • Last Modified:

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

0
suresh2000
Asked:
suresh2000
  • 2
1 Solution
 
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
 
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

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now