Extract characters from a string using SQL Server-CodePudding

I have a column that contains data like:

September 12, 2022    
September 15, 2022
December 12, 2022
January 31, 2023

and this is what I need from those string:

September 2022
September 2022
December 2022
January 2023

I only need the month and year of those already existing string values. How can I extract this using sql server?

Kindly help with coming up a solution for this.

I tried using trim but didnt work as expected.

CodePudding user response：

The solution, really, is to not store dates as strings; using the right data type means you get automatic validation (avoiding February 29, 6666 and who knows what else), and you get built-in date/time functionality (like extracting just the month name and year) without having to convert or decide what to do with invalid (or null after try_convert) output when the column inevitably gets polluted with garbage.

But if you're stuck with what you're stuck with, as long as you're using English language, you should be able to use convert with style 107:

SELECT CONCAT(DATENAME(MONTH, x), ' ', YEAR(x))
  FROM 
  (
    SELECT TRY_CONVERT(date, 'September 12, 2022', 107)
  ) AS x(x);

So, if you have a table:

SELECT CONCAT(DATENAME(MONTH, x), ' ', YEAR(x))
  FROM 
  (
    SELECT TRY_CONVERT(date, BadColumnDataType, 107)
    FROM dbo.YourTableName
  ) AS x(x);

You may want your code to explicitly say:

SET LANGUAGE us_english;

Working example in this fiddle. And an example of how it kind of works in other languages here.

CodePudding user response：

If your column is using date datatype, you may use this query:

SELECT FORMAT(<Your Column Name>, 'MMMM yyyy');

However, if you use varchar or nvarchar datatype, you can slightly change this query to:

SELECT FORMAT(CAST(<Your Column Name> AS date), 'MMMM yyyy');

FORMAT only works for SQL Server 2012 or later.

More information: https://learn.microsoft.com/en-us/sql/t-sql/functions/format-transact-sql?view=sql-server-ver16