CODEWITHSUNDEEP
sqlsql-server
ngoc
ngoc

Reputation: 63

Sort by text (nvarchar)

Not sure how to sort with column 'DateText' column like this:

April 2020
March 2001
May 2020
June 2021
December 2021

Expect result

December 2021
June 2021
March 2001
May 2020
April 2020

I tried this but no luck

ORDER BY 
    CASE WHEN 1 = ISNUMERIC(TextDate) 
            THEN CAST(TextDate AS INT) 
    END

Upvotes: 1

Views: 67

Answers (2)

Fiona Pye
Fiona Pye

Reputation: 28

To build a little on the good answer and advice given here, once the data is in a date datatype there are useful functions you can use to get specific information out of your date, such as the day of the week or the month name and so much more. These can then also be used to order your dates in many different ways should you require it!

Below I have made a little demo of the DATEPART and DATENAME functions, there are far more than this too. https://www.w3schools.com/sql/func_sqlserver_datename.asp

This was written and tested in T-SQL for MS SQL Server

DECLARE @Dates TABLE(textdate nvarchar(15))

INSERT INTO @Dates
    VALUES ('April 2020'),
        ('March 2001'),
        ('May 2020'),
        ('June 2021'),
        ('December 2021')

SELECT *,
    CONVERT(date, textdate) AS datetype
INTO #dates
FROM @Dates
ORDER BY CONVERT(date, textdate) desc

SELECT *,
    DATEPART(DD,datetype) AS DayNum,
    DATEPART(MM,datetype) AS MonthNum,
    DATEPART(YYYY,datetype) AS YearNum,
    DATENAME(WEEKDAY, datetype) AS DayName,
    DATENAME(MONTH, datetype) AS MonthName
FROM #dates
ORDER BY datetype desc

DROP TABLE #dates

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269963

Happily, your format can be converted to a date:

order by convert(date, textdate)

You actually want desc for your example.

Upvotes: 1

Related Questions