Search code examples
datet-sql

How to convert to Date format like 'January 2024' in T-SQL?

I am trying to convert datetime to a format like 'January 2024'.

I was able to convert format from datetime to date by using:

Convert(date, [ProcessingDate]).

Which I have the result as '2024-01-01'

How do I convert this into 'January 2024' in T-SQL?

The reason I am trying to create this format is that I need to concatenate with other varchar/text in T-SQL to be used in a drop down menu in PowerBI presentation layer.

For example: I am trying to create drop down menu like this:

...
April 2014 

May 2024

Current = June 2014

So, I am trying to concatenate with 'Current = '

Bottom one is what I have so far in PowerBI (May 2024 as latest/current month).

enter image description here

Solution

  • This is actually fairly easy if you haven't already found the answer.

    FORMAT(Convert(date, [ProcessingDate]), 'MMMMMMMMM yyyy');

    You may preferably want to remove the SQL Server-specific syntax and use:

    FORMAT(CAST([ProcessingDate] as DATE), 'MMMMMMMMM yyyy');

    Check out this stackoverflow article post about CAST vs CONVERT

    That is the most specific answer I can come up with. You can see that it would be easy to also specify a myriad of other common formatting styles between the single quotes of the format specifier. I would also suggest using the complete FORMAT function parameters and adding the language designator like so:

    FORMAT(CAST([ProcessingDate] as DATE), 'MMMMMMMMM yyyy', 'en-US');