sqlazuredatesql-date-functionssql-convert

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

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?

Solution

  • 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