Convert date to YYYYMM format

Formatting strategies for SQL Server

Stepping into the realm of SQL Server, let's explore alternate methodologies beyond the comfort zone of FORMAT:

Leverage CONVERT and varchar

In the absence of FORMAT, CONVERT emerges as the go-to strategy:

SELECT CONVERT(nvarchar(6), GETDATE(), 112) AS FormattedDate;  -- Just sitting here quietly, formatting your dates.


From chaos emerges a reliable YYYYMM string.

Integer mathematics— the high performance route

Paying due regard to performance, let's shun strings:

SELECT (YEAR(GETDATE()) * 100) + MONTH(GETDATE()) AS FormattedDate;  -- String is banished!


You get an integer masquerading as YYYYMM, possibly with a boost in the performance.

Combine year and month— because diversity

In works meant for posterity or when FORMAT isn't available:

SELECT CAST(DATEPART(yy, GETDATE()) AS varchar) + RIGHT('0' + CAST(DATEPART(mm, GETDATE()) AS varchar), 2) AS FormattedDate;


We ensure a peaceful coexistence of month parts with the RIGHT function.

When does the wand choose the wizard?

• FORMAT simplifies life and code with its readability and ease.
• Count on CONVERT when you are stuck with versions prior to 2012 of SQL Server.
• Integer mathematics paves the way for maximum performance.
• While pairing DATEPART, CAST, and RIGHT brings joy when string functions are more comforting.

Oops scenarios and word of caution

Remember that FORMAT enjoys a notoriety for being slower for large datasets. The integer math method sometimes is clumsy and returns a number instead of a string, which may steal the thunder away from your consistency parade.

Test for reliability

Ensure your method passes the test of time. Test for skewness with 31st December and leap babies:

SELECT FORMAT('2023-12-31', 'yyyyMM'); -- Should return 202312
SELECT FORMAT('2024-02-29', 'yyyyMM'); -- Should return 202402


Both should pass with flying colors so you can raise a toast to the reliability and accuracy of your date converting mechanism.

Enriching with hands-on tips and best practices

• Use comments in your SQL code to document the reason for choosing a particular method. Preservation of knowledge, quite cool huh?
• Performance tests are lifesavers while working with large datasets.
• Brush up your localization knowledge. Who knew, some day you might be solving problems with an international context!