How to convert nvarchar to date from one column to another in SQL Server

Question

I have a column called “Month Year Raw” that has multiple nvarchar type data like this: 202201 and I want to convert that to a column called “Month Year” that will be like “January 2022”.

Answer 1

This is really bad news. You shouldn't be storing dates as strings at all, never mind multiple times (which is just redundant storage for no gain). Even nvarchar is bad; why would you ever need to use Unicode characters to represent yyyyMM? I know 2020 was bad but you still can't use 4 poop emojis (? ? ? ? 03).

Store a date as a proper date/time data type, then you get built-in validation, sargable queries, date math, etc. You can always derive these display formats where they belong, at display time.

Plenty of other advice at Dating Responsibly.

Until you can fix this:

UPDATE dbo.BadIdeas 
SET [Month Year] = CONCAT
  (
    DATENAME(MONTH, CONVERT(datetime, 
       [Month Year Raw] + '01', 112)),
    ' ', 
    YEAR(CONVERT(datetime, 
       [Month Year Raw] + '01', 112))
  );

• Example db<>fiddle

Answer 2

If you're just wondering about the programmatic possibilities - "202201" would take the LEFT(4) chars for the year and RIGHT(2) chars for the month. Lets assume you were handed that non-atomic nvarchar string and just needed a one-time ad-hoc query.

You would not add a new column to the table, just select what's needed for output. There is a built-in SQL Server function called DATENAME() that convert numbers into month names. Since the data is stored in nvarchar you need to convert to a compatible integer in order to use those functions so I'll use CAST().

DECLARE @variable nvarchar(100) = '202201'

SELECT DATENAME(MONTH, CAST(RIGHT(@variable, 2) as int)) + ' ' + LEFT(@variable, 4) AS [Answer]

Results in output:

January 2022

---

A few things I will point out:

1. nvarchar costs 2 bytes of storage per character plus 2 bytes for the variable overhead. "202201" costs 14 bytes total. Alternatively, datetime only costs 8 bytes total storage.
2. The datetime datatype is the only datatype designed to work with SQL Server's built-in functions which is why you are being told to store dates in the correct datatype.
3. Combining month and year into the same custom string is a violation of 1NF since the data is not atomic. When you have non-atomic data you end up having to extract parts of string using LEFT() and RIGHT(), etc. If you need to use system functions you will have to transform the non-atomic data using CAST().

Example of using a datetime datatype instead:

DECLARE @variable2 datetime = '2022-01-01'
SELECT FORMAT (@variable2, 'MMMM yyyy')

Also results in:

January 2022

Using the correct datatype results in shorter code, and does not require extracting or transformation of data.