January 27, 2017 at 1:00 pm
Hello,
I have just taken a new DBA job so don't criticize the overall query. I'm just trying to get it work again since it has been failing since the last DBA left several months ago. You'll see there are several nested queries below. The "sub4" query which provides the last massaging of the data before I can spit it out is creating a column titled COMMENT_DATE. It is being converted from a SUBSTRING of an overall memo field (which is VARCHAR). When I run the query ** WITHOUT ** the WHERE clause at the bottom (which insures that only new records in the last day are moved), it runs just fine. When I add in the WHERE clause, I get a "Conversion failed when converting date and/or time from character string." error. For testing, you can see I have added both fields to the final output and compared the output and both look just fine. When I hover my mouse over both, it shows they are a DATETIME datatype. You can see I even inserted a CASE statement to test the comparison. The CASE statement works perfectly. This only fails when I add the WHERE clause back in at the bottom.
Any ideas why it thinks I'm converting anything from a character string when both values are already DATETIME?
SELECT sub4.MEMO_NO +
CONVERT(VARCHAR, CONVERT(DATE, sub4.COMMENT_DATE)) +
CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY sub4.MEMO_NO)) AS MM_NO,
sub4.COMMENT_DATE,DATEADD(DD, -1, GETDATE()),
CASE
when sub4.COMMENT_DATE >= DATEADD(DD, -1, GETDATE()) then 'false'
else 'true'
END as test,
*
FROM (SELECT sub3.DATESTAMP AS INS_DATESTAMP,
sub3.MEMO_DATE,
CONVERT(DATETIME, SUBSTRING(sub3.PART3, CHARINDEX('|||', sub3.PART3)+3, 14)) AS COMMENT_DATE,
sub3.MEMO_NO,
sub3.MEMO_ID,
sub3.MEMO_TYPE,
sub3.MEMO_SUBTYPE,
CASE WHEN sub3.PART1 LIKE '%TV%' THEN 'TV'
WHEN sub3.PART1 LIKE '%WEB%' THEN 'WEB'
WHEN sub3.PART1 LIKE '%RADIO%' THEN 'RADIO'
WHEN sub3.PART1 LIKE '%PRINT%' THEN 'PRINT'
WHEN sub3.PART1 LIKE '%OTHER%' THEN 'OTHER'
END AS EVENT_TYPE,
sub3.PART2 AS EVENT_LOC,
SUBSTRING(sub3.PART3, 1, CHARINDEX('|||', sub3.PART3)-1) AS ADDTL,
sub3.MEMO AS FULL_MEMO
FROM (SELECT sub2.DATESTAMP,
sub2.MEMO_NO,
sub2.MEMO_ID,
sub2.MEMO_DATE,
sub2.MEMO_TYPE,
sub2.MEMO_SUBTYPE,
sub2.MEMO,
sub2.PART1,
SUBSTRING(sub2.PART2, 1, CHARINDEX('|', sub2.PART2)-1) AS PART2,
SUBSTRING(sub2.PART2, CHARINDEX('||', sub2.PART2)+2, LEN(sub2.MEMO)) AS PART3
FROM (SELECT sub.DATESTAMP,
sub.MEMO_NO,
sub.MEMO_ID,
sub.MEMO_DATE,
sub.MEMO_TYPE,
sub.MEMO_SUBTYPE,
sub.MEMO,
SUBSTRING(sub.MEMO, 1, CHARINDEX('|', sub.MEMO)-1) PART1,
SUBSTRING(sub.MEMO, CHARINDEX('|', sub.memo)+1, LEN(sub.MEMO)) PART2
FROM (SELECT CAST(GETDATE() AS DATE) AS DATESTAMP,
m.MEMO_NO AS MEMO_NO,
m.MEMO_ID,
m.MEMO_DATE,
m.MEMO_TYPE,
m.MEMO_SUBTYPE,
LTRIM(RTRIM(s.Item)) AS MEMO
FROM SYSADM.MEMO AS m
CROSS APPLY dbo.Split('||||', m.MEMO_TEXT) AS s
WHERE (s.Item != '')
AND (m.MEMO_TYPE = 'MEDIA')
AND (m.MEMO_SUBTYPE is null)
AND (m.MEMO_DATE >= '01-JUN-2015')
AND (m.MEMO_NO NOT IN ('M15-713176', 'M16-734104', 'M16-773057'))) sub
WHERE (CHARINDEX('|', sub.MEMO) > 1)) sub2
WHERE (CHARINDEX('|', sub2.PART2) > 1)) sub3
WHERE (CHARINDEX('|||', sub3.PART3) > 1)) sub4
WHERE (sub4.COMMENT_DATE >= DATEADD(DD, -1, GETDATE()))
January 27, 2017 at 1:06 pm
Are you able to provide any sample data which shows the error occurring? There could be a number of reasons the conversion is failing, but without data it's a bit of guess work.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
January 27, 2017 at 1:08 pm
Thom A - Friday, January 27, 2017 1:06 PMAre you able to provide any sample data which shows the error occurring? There could be a number of reasons the conversion is failing, but without data it's a bit of guess work.
The fields are already in DATETIME format which is what has me puzzled. It shouldn't be trying to do any type of conversion.
January 27, 2017 at 1:50 pm
So here is an update to the issue that makes absolutely no sense to me. Just out of curiosity, I created an outer select and put the entire contents of the query without the WHERE clause in it. I put the WHERE clause as part of the outer select and it works as expected. So at least I can move forward but I still want to know why it thought it was doing a conversion that it didn't need to do? Here is what I have now that works:
SELECT *
FROM (
SELECT sub4.MEMO_NO +
CONVERT(VARCHAR, CONVERT(DATE, sub4.COMMENT_DATE)) +
CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY sub4.MEMO_NO)) AS MM_NO,
*
FROM (SELECT sub3.DATESTAMP AS INS_DATESTAMP,
sub3.MEMO_DATE,
CONVERT(DATETIME, SUBSTRING(sub3.PART3, CHARINDEX('|||', sub3.PART3)+3, 14)) AS COMMENT_DATE, -- Added fixed length to the SUBSTRING to prevent failure due to missing |||| at the end of the comment line. This assumes dates and times will ALWAYS be MM/DD/YYYY HH:MM. - JP 01/26/2017
sub3.MEMO_NO,
sub3.MEMO_ID,
sub3.MEMO_TYPE,
sub3.MEMO_SUBTYPE,
CASE WHEN sub3.PART1 LIKE '%TV%' THEN 'TV'
WHEN sub3.PART1 LIKE '%WEB%' THEN 'WEB'
WHEN sub3.PART1 LIKE '%RADIO%' THEN 'RADIO'
WHEN sub3.PART1 LIKE '%PRINT%' THEN 'PRINT'
WHEN sub3.PART1 LIKE '%OTHER%' THEN 'OTHER'
END AS EVENT_TYPE,
sub3.PART2 AS EVENT_LOC,
SUBSTRING(sub3.PART3, 1, CHARINDEX('|||', sub3.PART3)-1) AS ADDTL,
sub3.MEMO AS FULL_MEMO
FROM (SELECT sub2.DATESTAMP,
sub2.MEMO_NO,
sub2.MEMO_ID,
sub2.MEMO_DATE,
sub2.MEMO_TYPE,
sub2.MEMO_SUBTYPE,
sub2.MEMO,
sub2.PART1,
SUBSTRING(sub2.PART2, 1, CHARINDEX('|', sub2.PART2)-1) AS PART2,
SUBSTRING(sub2.PART2, CHARINDEX('||', sub2.PART2)+2, LEN(sub2.MEMO)) AS PART3
FROM (SELECT sub.DATESTAMP,
sub.MEMO_NO,
sub.MEMO_ID,
sub.MEMO_DATE,
sub.MEMO_TYPE,
sub.MEMO_SUBTYPE,
sub.MEMO,
SUBSTRING(sub.MEMO, 1, CHARINDEX('|', sub.MEMO)-1) PART1,
SUBSTRING(sub.MEMO, CHARINDEX('|', sub.memo)+1, LEN(sub.MEMO)) PART2
FROM (SELECT CAST(GETDATE() AS DATE) AS DATESTAMP,
m.MEMO_NO AS MEMO_NO,
m.MEMO_ID,
m.MEMO_DATE,
m.MEMO_TYPE,
m.MEMO_SUBTYPE,
LTRIM(RTRIM(s.Item)) AS MEMO
FROM SYSADM.MEMO AS m
CROSS APPLY dbo.Split('||||', m.MEMO_TEXT) AS s
WHERE (s.Item != '')
AND (m.MEMO_TYPE = 'MEDIA')
AND (m.MEMO_SUBTYPE is null)
AND (m.MEMO_DATE >= '01-JUN-2015')
AND (m.MEMO_NO NOT IN ('M15-713176', 'M16-734104', 'M16-773057'))) sub
WHERE (CHARINDEX('|', sub.MEMO) > 1)) sub2
WHERE (CHARINDEX('|', sub2.PART2) > 1)) sub3
WHERE (CHARINDEX('|||', sub3.PART3) > 1)) sub4
) AS q
WHERE (q.COMMENT_DATE >= DATEADD(DD, -1, GETDATE()))
January 28, 2017 at 12:27 am
Quick thought, change the WHERE clause and convert the COMMENT_DATE to a DATE, just as you do in the output.
😎
WHERE (CONVERT(DATE, q.COMMENT_DATE) >= DATEADD(DD, -1, GETDATE()))
January 28, 2017 at 12:56 pm
You removed this part:
CASE
when sub4.COMMENT_DATE >= DATEADD(DD, -1, GETDATE()) then 'false'
Because your WHERE clauses imply table scans anyway, optimiser might choose to do all calculations at once when the table is scanned, and before the rows are filtered
_____________
Code for TallyGenerator
January 28, 2017 at 4:41 pm
Eirikur Eiriksson - Saturday, January 28, 2017 12:27 AMQuick thought, change the WHERE clause and convert the COMMENT_DATE to a DATE, just as you do in the output.
😎
WHERE (CONVERT(DATE, q.COMMENT_DATE) >= DATEADD(DD, -1, GETDATE()))
I did try that but still got that same error.
January 28, 2017 at 4:43 pm
Sergiy - Saturday, January 28, 2017 12:56 PMYou removed this part:CASE
when sub4.COMMENT_DATE >= DATEADD(DD, -1, GETDATE()) then 'false'Because your WHERE clauses imply table scans anyway, optimiser might choose to do all calculations at once when the table is scanned, and before the rows are filtered
Yes, I did remove the CASE statement from the output. The output as you see now was the original. I only put that in there for testing.
I hadn't thought about how the optimiser runs. Interesting thought though. If I have some time, I'll dump sub4 into a temp table so the correct DATETIME format is there and then run the query again just to see.
February 1, 2017 at 12:17 pm
When I dumped sub4 to a temp table before doing the final select, the WHERE clause worked as expected. So, the optimizer theory is the likely culprit.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply