[SOLVED] Converting VARCHAR 'MMM-dd-yyyy' to DATE 'yyyymmdd' in SQL

Converting VARCHAR 'MMM-dd-yyyy' to DATE 'yyyymmdd' in SQL

Need help with conversion of a varchar value of 'MMM-dd-yyyy' to a DATE datatype in SSMS.

For example: from 'JAN-01-2025' to '20250101'.

I tried:

SELECT CONVERT(DATE, 'JAN-01-2025', 112)

However, it returned an error:

Conversion failed when converting date and/or time from character string.

Solution

A quick look at the documentation tells you why your attempt failed:

Without century (yy) With century (yyyy) Standard Input/output

12 112 ISO 12 = yymmdd
112 = yyyymmdd

Without century (yy)	With century (yyyy)	Standard	Input/output
12	112	ISO	12 = yymmdd 112 = yyyymmdd

As you state in your title, your format is MMM-dd-yyyy which is nothing like yyyyMMdd...

The closest style to your value is style 107, MMM dd, yyyy. The comma, however, is optional for this style, so it also supports MMM dd yyyy. As such you can just REPLACE your hyphens (-) with spaces and it'll work:

SELECT TRY_CONVERT(date, REPLACE('JAN-01-2025','-',' '), 107);

I use TRY_CONVERT to avoid an error, as storing date (and time) values as a stirng is a well known design flaw, and likely you will have low quality data, with invalid date values.