How to write out/convert YYYY-MM-DD to "Month, DD, YYYY" in SQL

Question

I'm trying to convert some dates I have that written as 2004-01-05 into January 1, 2004.

I can't use DATEFORMAT() because it's not a supported built in function in my database. I'm using the up-to-date version of Azure for my database. If I try to use DATE_FORMAT(), I get the error:

date_format is not a recognized built-in function name

I've already done

SELECT CONVERT(VARCHAR(20), startdate, 107)
FROM trial_program

But I only get "Jan 01, 2004", which is close but not what I want.

How do I write out a whole date as January 1, 2004 from 2004-01-05?

Answer 1

Assuming you have a date in a date or datetime then FORMAT will format dates for you.

For example:

select format(getdate(),'MMMM dd, yyyy')` -- June 22, 2023

See http://sqlfiddle.com/#!18/5f342/477

Reference:

FORMAT

Custom date and time format strings

Answer 2

SELECT CONCAT(
    DATENAME(month, startdate), 
    ' ', 
    CAST(DAY(startdate) AS VARCHAR(2)), 
    ', ', 
    CAST(YEAR(startdate) AS VARCHAR(4))
)
FROM trial_program;