Convert an AM/PM Time from String to Time-CodePudding

I have a table with a Time column that is a string [nvarchar(50)] with the following values.

5:34 AM
4:06 PM
7:14 PM
10:25 PM
2:12 AM

In my query, I am trying to order the various rows in ascending order but, because it is a string, it's ordering it in alphabetical order.

Is there a function I can use in the ORDER statement that will convert the string to a time or a datetime variable? I have tried using the CAST and CONVERT with no luck.

CAST([Time] AS datetime)

Convert(nvarchar(50),[Time],101)

Thanks!

CodePudding user response：

You can use TRY_CONVERT() to sort by the right type (and avoid errors with garbage that got into your table):

DECLARE @BadDesign table(PoorChoice nvarchar(50));

INSERT @BadDesign(PoorChoice) VALUES
(N'5:34 AM'),
(N'4:06 PM'),
(N'7:14 PM'),
(N'10:25 PM'),
(N'2:12 AM'),
(N'