It looks like there isn't a direct convert from date datatype to 'D-Mon-YYYY' format.

The closest one I can get is 'DD Mon YYYY':

declare @IncomingDate Date = '2010-04-01'
select convert(varchar(20), @IncomingDate, 106)
--01 Apr 2010

I can get to 'DD-Mon-YYYY' if I replace ' ' with '-' as shown below:

declare @IncomingDate Date = '2010-04-01'
select replace(convert(varchar(20), @IncomingDate, 106),' ', '-')
--01-Apr-2010

I end up writting a function to get the end format:

CREATE FUNCTION [dbo].[ufnFormatDate] (@IncomingDate Date)
RETURNS Varchar(11)
AS
BEGIN
Return (DATENAME(Day, @IncomingDate) + '-' + SUBSTRING(DATENAME(Month,@IncomingDate),1,3) + '-' + Cast(DATEPART(yyyy, @IncomingDate) as Char(4)))
END

declare @IncomingDate Date = '2010-04-01'
select [dbo].[ufnFormatDate] (@IncomingDate)
--1-Apr-2010

Now the problem is the function will be called tons of time every time a SP returns data. I wonder if there is a better way to convert the date format in this case.