How can I format a datetime to varchar with the format dd-MMM-yyyy?

Question

I feel this should be a simple solution but I'm having trouble finding anything on it...

So: How do I get from a datetime to a varchar with the format dd-MMM-yyyy?

Eg: my_date --> '29-May-2012'

The closes I've managed to come is

convert(varchar(20),my_date,105) --> '29-05-2012'

Yes, I DO need to do this right in T-SQL.

Answer 1

This format isn't natively supported, but from Build a cheat sheet for SQL Server date and time formats, the closest is probably:

SELECT REPLACE(CONVERT(CHAR(11), GETDATE(), 106), ' ', '-');

A previous version of this answer recommended FORMAT(), but I have since experimented and believe that's a bad idea at scale unless you just want all your queries to take twice as long.

Answer 2

You could use:

SELECT replace(convert(char(11), getdate(), 113), ' ', '-')

or

SELECT replace(convert(char(11), getdate(), 106), ' ', '-')