Conversion failed when converting the nvarchar value 'Dec' to data type int

1

hi everyone
i amd trying as below

SELECT * FROM
( SELECT
LBrCode, PrdAcctId, EffFromDate, DisbursedDate, DisbursedAmt, InstlStartDate,

CASE WHEN (day(InstlStartDate)>28 AND month(InstlStartDate)=02 AND (CAST(year(InstlStartDate) AS INT)/4)<>0)
THEN convert(datetime,(day(isnull(InstlStartDate,'01-01-1950')-1)+'-'+substring(DATENAME(month,isnull(InstlStartDate,'01-01-1950')),1,3)+'-'+year(getdate())),103)
ELSE
convert(datetime,(day(isnull(InstlStartDate,'01-01-1950'))+'-'+substring(DATENAME(month,isnull(InstlStartDate,'01-01-1950')),1,3)+'-'+year(getdate())) ,103)
END hrk,
InstlAmt, InstlIntComp,
rank() OVER (PARTITION BY LBrCode,PrdAcctId ORDER BY EffFromDate DESC) rnk
FROM llimit WHERE EffFromDate<=getdate()
) x WHERE rnk=1
but getting error as captioned

please help

2

The trouble is in the column which starts with the CASE expression. Couple of observations:

The case expression seems to be unnecessary. In the WHEN part of it, you are checking whether the day is 29 or greater of February in a non-leap year.

In the else part you appear to be trying to convert InstlStartDate to the dd/mm/yyyy format. If that is the case, you can do it more succinctly as
CONVERT(VARCHAR(20), COALESCE(InstlStartDate,'19500101'),103)

So my recommendation would be to remove the entire case expression for column hrk and replace it with the above, so the query becomes something like shown below:

...
InstlStartDate,
CONVERT(VARCHAR(20), COALESCE(InstlStartDate,'19500101'),103) as hrk,
InstlAmt,
...

Usually it is customary (at least for me) to use 19000101 instead of 19500101 for default dates.

3

thanks a lot sir

4

The DAY() and MONTH() functions will only work on a valid date. Thus, your code will never be able to correct a bad date even if one was there.

So, just do the conversion directly. However, since you want the result to be a datetime and not char, all you need to do is this:

CONVERT(datetime, ISNULL(InstlStartDate, '19500101'))

You don't have to convert to a character format at all.

1 Like