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

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

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.

thanks a lot sir

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.