July 7, 2023 at 5:22 am
From the code, A and B are the same table. So, why are you making SQL jump through hoops when joining the column to itself. Simply join ON A.validon = B.validon AND ...
July 7, 2023 at 5:23 am
If you insist on casting your key to a datetime, then you need to find the values that SQL is having difficulty converting.
Try this snippet to see what data it has an issue with
SELECT A.validon
FROM Tbl_PH AS A
WHERE TRY_CAST(CAST(A.validon AS varchar(6)) + '01' AS datetime) IS NULL
GROUP BY A.validon
July 7, 2023 at 10:01 am
sorry, one again join table on leave like this
INNER JOIN Tbl_Loan C with(nolock) on
CONVERT(varchar(8), DATEADD(MONTH, 12, CAST(CAST(A.validon AS varchar(6)) + '01' AS datetime)), 113) =
(CAST( C.validon AS varchar(6)) + '01')
i have upload image is describe data tbl_loan and tbl_PH and table STG_RECOVERY , what doesn't maybe because tbl_ph has format valid_on 'yyyymmdd' whereas tbl_loan has fomat valid_on 'yyyymm' ?
July 7, 2023 at 11:01 am
first - what is the datatype of validon in both tables?
and secondly - I'm sure the answer to the first question will tell us it is a int or a char (not a date).
this being the case
if a char, then you should be using "on a.validon like c.validon + '%'" or "on left(a.validon, 4) = c.validon"
if a int "on a.validon / 100 = c.validon" (the /100 will remove the 2 right most digits from the value which can then be directly compared to the other table.
but regardless you still seem to have "invalid" data on one or both tables so try the following (based on Des code)
SELECT A.validon
FROM Tbl_PH AS A
WHERE try_convert(date, left(convert(varchar(20), A.validon), 6) + '01', 112) IS NULL
GROUP BY A.validon
SELECT A.validon
FROM Tbl_Loan AS A
WHERE try_convert(date, left(convert(varchar(20), A.validon), 6) + '01', 112) IS NULL
GROUP BY A.validon
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply