July 7, 2023 at 3:40 am
hallo all, let me ask
i have a query convert like this :
INSERT INTO STG_RECOVERY
(MODEL, VALID_ON, NOREK, KOL_AWAL, KOL_AKHIR, BD_AWAL, BD_AKHIR, BD_KL_AWAL, BD_KL_AKHIR, AMOUNT, RECOVERY, RECOVERY_PCT)
SELECT 3, A.validon VALID_ON,
A.Acct,
A.BIKOLE KOL_AWAL,
B.BIKOLE KOL_AKHIR,
A.BAKI_DEBET BD_AWAL,
B.BAKI_DEBET BD_AKHIR,
NULL BD_KL_AWAL,
NULL BD_KL_AKHIR,
C.BD_KL AMOUNT,
A.BAKI_DEBET - B.BAKI_DEBET RECOVERY,
((A.BAKI_DEBET - B.BAKI_DEBET) / A.BAKI_DEBET) * 100 RECOVERY_PCT
FROM Tbl_PH A
JOIN Tbl_PH B with(nolock) ON CONVERT(varchar(8),
DATEADD(MONTH, 12, CAST (CAST(A.validon AS varchar(6)) + '01' AS datetime)), 112) =
CONVERT(varchar(8),
DATEADD(MONTH, 12, CAST (CAST(B.validon AS varchar(6)) + '01' AS datetime)), 112)
AND A.ACCT = B.ACCT
AND
(A.BAKI_DEBET > 0) AND (B.BAKI_DEBET > 0)
AND
(A.BAKI_DEBET > B.BAKI_DEBET )
INNER JOIN Recovery_kl_bd C
ON A.VALIDON = C.VALIDON AND A.ACCT = C.ACCT;
when I execute an error occurs with the message
'Msg 241, Level 16, State 1, Line 122
Conversion failed when converting date and/or time from character string'.
even though in the previous query I did convert validon it can, it's just different from the join table, previously it was in tbl_loan. and the data type is the same, namely int.
please help convert format like what should I do ?
thanks
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply