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.

Solution 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.

Solution 2

You could use:

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

or

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

Comments

[KM.]

why varchar(20) ? why even varchar? the format is fixed length.

[Taryn]

@KM. updated my answer, I just threw on a length when I initially wrote it

[GilM]

I think he was just echoing the OP. I'd probably use char(11) to be explicit. But, perhaps the intent was to populate a varchar(20) column.

[Adir D]

Why convert varchar(25) then take left(11)? Should just be char(11).

[Taryn]

@AaronBertrand you are correct, I fixed it. I should know better you like to call me out on that one. :)

[Adir D]

Well, it's just more verbose, and probably more expensive at some nanosecond level - not that any human would ever notice. But why say more than you have to? :-)

[Taryn]

@AaronBertrand I agree, I guess it is just a bad habit.

[AR.]

106! Simple enough, thanks. Accepting this answer for the 'future-proofing.' :)