Convert Text to Date and Order By Date

[shank]

I need to convert the text field [ShipDate] to date format. That seems to work. But how do I sort by that ShipDate field as a date format? Neither of my attempts fails.

Thanks!

SELECT DISTINCT CONVERT(VARCHAR(10), ShipDate, 101) as ShipDate
FROM [CarrierShipSummary]
ORDER BY ShipDate DESC

SELECT DISTINCT CONVERT(VARCHAR(10), ShipDate, 101) as ShipDate
FROM [CarrierShipSummary]
ORDER BY CONVERT(VARCHAR(10), ShipDate, 101) as ShipDate DESC

[shank]

I found a solution, but it doesn't look correct. I'm assuming there's a better way than this, correct?

Thanks!

SELECT DISTINCT CONVERT(VARCHAR(10), ShipDate, 101) as ShipDate, CAST([ShipDate] as DateTime) as SortDate
FROM [CarrierShipSummary]
ORDER BY cast([ShipDate] as datetime) DESC

[yosiasz]

Please provide sample data. Helpnus help you

[shank]

The ShipDate field is simply text:
04/14/2020
04/13/2020
04/12/2020
04/11/2020

thanks

[yosiasz]

so the way we encourage people to provide sample data is as follows

create table #sample(ShipDate  varchar(50))

insert into #sample
select '04/14/2020'

Please provide sample as shown above. Its a good habit to get into.
We do not have direct access to your database and we are volunteering to help you.
So a little effort on your part would be appreciated

[ScottPletcher]

SELECT DISTINCT CONVERT(VARCHAR(10), ShipDate, 120) as ShipDate
FROM [CarrierShipSummary]
ORDER BY CONVERT(VARCHAR(10), ShipDate, 120) DESC

--or, even better (because it avoids possible date conversion errors)

SELECT DISTINCT SUBSTRING(ShipDate, 7, 4) + '-' + LEFT(ShipDate, 2) + '-' +
SUBSTRING(ShipDate, 4, 2) AS ShipDate
FROM [CarrierShipSummary]
ORDER BY SUBSTRING(ShipDate, 7, 4) + '-' + LEFT(ShipDate, 2) + '-' +
SUBSTRING(ShipDate, 4, 2) DESC

[shank]

Very interesting and much appreciated.

Thanks!