Sort week wise date in string format

Question

I have data in a table with two columns week and amount as shown below

Week                      Amount
--------------------------------
1w - 1m - 2015             1000
4w - 8m - 2015             1000
2w - 2m - 2015             1000
4w - 6m - 2015             1000
3w - 3m - 2015             1000
3w - 10m - 2015            1000
1w - 10m - 2015            1000
3w - 12m - 2015            1000
1w - 6m - 2016             1000
2w - 6m - 2016             1000
3w - 6m - 2016             1000
4w - 6m - 2016             1000
3w - 8m - 2016             1000
2w - 9m - 2016             1000

How can I sort this table by week (as date)?

My desired output is:

     Week                    Amount
    --------------------------------
    1w - 1m - 2015             1000
    2w - 2m - 2015             1000
    3w - 3m - 2015             1000
    4w - 6m - 2015             1000
    4w - 8m - 2015             1000
    1w - 10m - 2015            1000
    3w - 10m - 2015            1000
    3w - 12m - 2015            1000
    1w - 6m - 2016             1000
    2w - 6m - 2016             1000
    3w - 6m - 2016             1000
    4w - 6m - 2016             1000
    3w - 8m - 2016             1000
    2w - 9m - 2016             1000

Show source
| sql-server   | sql   2017-01-02 19:01 2 Answers

Answers to Sort week wise date in string format ( 2 )

  1. 2017-01-02 19:01
    Select *
     From  YourTable
     Order by right([Week],4)
             ,cast(substring([Week],charIndex('m',[Week])-2,2) as int)
             ,left([Week],1)
    

    Returns

    week            Amount
    1w - 1m - 2015  1000
    2w - 2m - 2015  1000
    3w - 3m - 2015  1000
    4w - 6m - 2015  1000
    4w - 8m - 2015  1000
    1w - 10m - 2015 1000
    3w - 10m - 2015 1000
    3w - 12m - 2015 1000
    1w - 6m - 2016  1000
    2w - 6m - 2016  1000
    3w - 6m - 2016  1000
    4w - 6m - 2016  1000
    3w - 8m - 2016  1000
    2w - 9m - 2016  1000
    
  2. 2017-01-02 19:01

    Bulding up on accepted answer, if table structure can be changes (add some columns), it can be made to store data in much more friendly way, by using persisted computed columns. This costs some storage and some extra computation effort on data insertion, but it will make most future selection faster, since string parsing is no longer required. Also, each column has the appropriate type, so it is harder to get invalid data.

    Setup:

    create table WeekData
    (
        WeekStr VARCHAR(16) NOT NULL,
        Amount INT,
        YearVal AS CAST (Right(WeekStr,4) AS SMALLINT) PERSISTED,
        MonthVal AS cast(substring(WeekStr,charIndex('m', WeekStr)-2,2) as SMALLINT),
        WeekVal AS CAST(left(WeekStr,1) AS TINYINT)
    )
    
    insert into WeekData (WeekStr, Amount) 
    VALUES ('1w - 1m - 2015', 1000),
    ('4w - 8m - 2015', 1000),
    ('2w - 2m - 2015', 1000),
    ('4w - 6m - 2015', 1000),
    ('3w - 3m - 2015', 1000),
    ('3w - 10m - 2015', 1000),
    ('1w - 10m - 2015', 1000),
    ('3w - 12m - 2015', 1000),
    ('1w - 6m - 2016', 1000),
    ('2w - 6m - 2016', 1000),
    ('3w - 6m - 2016', 1000),
    ('4w - 6m - 2016', 1000),
    ('3w - 8m - 2016', 1000),
    ('2w - 9m - 2016', 1000)
    GO
    

    Select

    select WeekStr, Amount 
    from WeekData
    order by YearVal, MonthVal, WeekVal
    

Leave a reply to - Sort week wise date in string format

◀ Go back