String with dd-MM-yyyy hh:mm:ss format to datetime in SQL Server-CodePudding

I'm trying to convert a nvarchar(max) column to a datetime in SQL Server. The format is dd-MM-yyyy hh:mm:ss, which I'd like to keep, however I'd like to change the type to datetime. I've found many similar questions about this conversion, but not with my particular format.

I've tried CONVERT(nvarchar(MAX), string_column, 121), however without luck. How can this be done? As example, I'd like to convert the string 26-11-2021 08:19:16 to datetime.

CodePudding user response：

This works for me:

SELECT CONVERT(datetime2(0), '26-11-2021 08:19:16', 105);
/* Output: 2021-11-26 08:19:16 */

Recent versions of SQL Server also have the TRY_PARSE method:

SELECT TRY_PARSE('26-11-2021 08:19:16' As datetime2(0) USING 'en-GB')
/* Output: 2021-11-26 08:19:16 */

CodePudding user response：

 DECLARE @InString NVARCHAR(20)='26-11-2021 08:19:16';
 SELECT @InString;
 SELECT CONVERT(DATETIME,@InString,105);

Could you please try if it is suitable for you