user3115933 user3115933 - 7 months ago 14
SQL Question

What is the most simple T-SQL syntax to convert a datetime column into the 'MMM YYYY" format?

I am using SQL Server 2014 and I need to add a line to my SQL query that will convert a column called StayDate into the "MMM YYYY" format.
The StayDate column is in

datetime
format (eg: 2016-06-01 00:00:00.000)

Basically, I need the output to be "Jul 2016" (from example above).

I have tried playing around with the following code:

Format (StayDate, "MMM DD YYYY")


which I converted into:
Format (StayDate, "MMM YYYY")


But I end up with the following result: Jul YYYY

I like the simplicity of the above code a lot. Is there a workaround using the
Format
syntax?

Answer

The format function takes a .NET format string, so the four digit year part has to be in lowercase, like this:

Format(StayDate, "MMM yyyy")

(reference: https://msdn.microsoft.com/de-de/library/hh213505(v=sql.120).aspx)

Comments