May 11, 2007 at 9:03 am
Hello, The following programme runs fine in SQL 2000 and failing on 2005.
Error is in subject line.
Error is
"Conversion failed when converting datetime from character string." (Tried CAST and CONVERT with no use)
------------------
declare
@vchrTraceFile nvarchar(128)
, @vchrFileName nvarchar(128)
, @vchrFileName1 nvarchar(128)
, @vchrBulkCmd varchar(300)
, @vchrCmd varchar(100)
, @vchrErrorMsg varchar(100)
, @vchrSrvName varchar(100)
, @vchrSQLStr Varchar(500)
, @vchrPath varchar(100)
, @dtDiff datetime
SET
@dtDiff = getdate()- 8
SET
@vchrSQLStr=''
-- Deletes 8 days older Reports
set
@vchrSrvName=@@Servername
SET
@vchrPath ='\\'+@vchrSrvName+'\Reports'
CREATE
TABLE Reports(DirInfo VARCHAR(7000))
SELECT
@vchrCmd = 'dir ' + @vchrPath + ' /OD'
INSERT
INTO Reports EXEC master.dbo.xp_cmdshell @vchrCmd
Declare
csrFileNames CURSOR FOR
SELECT LTRIM(RTRIM(SUBSTRING(DIRINFO,39,LEN(DIRINFO)-38))) FileName FROM Reports
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) <> 0 AND SUBSTRING(DIRINFO,25,5)<>'<DIR>'
and SUBSTRING(DirInfo, 1, 10)< @dtDiff
OPEN csrFileNames
FETCH NEXT FROM csrFileNames Into @vchrFileName
WHILE @@FETCH_STATUS = 0
BEGIN
set @vchrSQLStr = 'EXEC master.dbo.xp_cmdshell ''DEL ' + @vchrPath +'\'+ '"'+@vchrFileName+'"'+''''
print(@vchrSQLStr)
FETCH NEXT FROM csrFileNames Into @vchrFileName
END
Close
csrFileNames
DeAllocate
csrFileNames
DROP
TABLE Reports
---------------------
Can any one help me in correcting the error.
Thanks
May 11, 2007 at 9:35 am
You didn't tell us if the data in 2000 and 2005 are the same, so for now I'll assume they aren't. If they are, then it's likely a difference in how the two implicitly convert to datetime values.
I'm guessing your problem is with the DirInfo column in the Reports table on your 2005 box. While you're testing with IsDate in the WHERE clause, it's in the same WHERE clause, so I'm guessing the date comparison you do is even happening on columns that don't meet your IsDate criteria.
An easy way to test that is to turn that statement into a derived table, leaving out that DirInfo < @dtDiff portion, and putting that bit in the wrapper around the derived table.
May 11, 2007 at 9:38 am
In case I didn't describe that well enough to understand, I meant something like this:
SELECT
Filename
FROM
(
SELECT LTRIM(RTRIM(SUBSTRING(DIRINFO,39,LEN(DIRINFO)-38))) FileName, DirInfo FROM Reports
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) <> 0 AND SUBSTRING(DIRINFO,25,5)<>'<DIR>'
)
WHERE
SUBSTRING(DirInfo, 1, 10)< @dtDiff
May 11, 2007 at 9:48 am
David,
Data is the same in both the cases, i mean in 2000 and 2005. and when i compare with
"SUBSTRING(DirInfo, 1, 10)< @dtDiff" this is failing in 2005.
tried with other query you sent with same result.
Thanks
May 11, 2007 at 9:54 am
What does the following show in each environment?
SELECT
InvalidDate = SubString(DirInfo, 1, 10)
FROM
Reports
WHERE
IsDate(SubString(DirInfo, 1, 10)) = 0
May 11, 2007 at 9:55 am
David,
AS workaround i am changing the code to make it work as below. Now this is owrking in 2005.
But let me know if any other better solution exists.
-----------------
Create
table TestDt1(DirInfo1 datetime,filename1 varchar(200))
insert
into TestDt1
SELECT
SUBSTRING(DirInfo, 1, 10),LTRIM(RTRIM(SUBSTRING(DIRINFO,39,LEN(DIRINFO)-38))) FileName FROM Testdt
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) <> 0 AND SUBSTRING(DIRINFO,25,5)<>'<DIR>'
select
* from TestDt1 where dirinfo1 < getdate()-8
--------------------
May 11, 2007 at 10:04 am
My query should have done the same thing, as the IsDate should have been applied before the date comparison is performed. I do know that implicit conversions are nondeterministic in 2005, which is why I guessed that it was an implicit conversion issue in my first response (if the data was the same). Running my recent query where it gets invalid dates should give us more info as to where it is having the hiccups.
Alternatively, you can do a Cast (or Convert) to the Substring(DirInfo, 1, 10) portion of the WHERE clause in my query, thus forcing an explicit conversion.
May 11, 2007 at 10:14 am
David,
Even the Second query you sent throwing the same error. Thanks a lot for your help. for now, I am going with work around as i said in my last response, but if you can come up with better solution , please do let me know.
Thanks again.
May 11, 2007 at 10:44 am
This throws the same error? I don't see how that could be the case.
SELECT
InvalidDate = SubString(DirInfo, 1, 10)
FROM
Reports
WHERE
IsDate(SubString(DirInfo, 1, 10)) = 0
May 11, 2007 at 12:22 pm
Sorry David.. I was referring to the query you sent.
SELECT
Filename
FROM
(
SELECT LTRIM(RTRIM(SUBSTRING(DIRINFO,39,LEN(DIRINFO)-38))) FileName, DirInfo FROM Reports
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) <> 0 AND SUBSTRING(DIRINFO,25,5)<>'<DIR>'
)
WHERE
SUBSTRING(DirInfo, 1, 10)< @dtDiff
May 11, 2007 at 12:29 pm
Can you post 10-20 rows of data that my InvalidDate query returns? There shouldn't be any proprietary info issues, as it should just return dates, but if for some reason the results have any client info, etc., feel free to describe their contents or modify them so they aren't recognizable.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply