Sorting text that looks like date on asp page

I have created quite a mess here. Here are the details:

I have a SQL SERVER 2008 view that pulls a a bunch of information but one of those columns is a text field that I strip some characters off the front and back. It looks like this in the SELECT statement:

ISNULL(SUBSTRING(dbo.Job_Operation.Note_Text, 11, 15), 'No Date') AS Expr2

Open in new window


It returns results that look like this:

10/27/2015 1530
10/16/2015 1530
10/19/2015 1530
10/23/2015 1530
10/27/2015 1530
**Stocking Agre
**Stocking Agre
No Date
11/04/2015 1530
11/04/2015 1530
11/05/2015 1530
09/16/2015 1530
09/16/2015 1530
01/16/2016 1530
01/16/2016 1530

In an ASP page I display this information in a table as sort the table using the Expr2 column.

Being that this is a text column it sorts the "**Stocking Agre" at the top and the "No Date" at the bottom and puts all the dates in between. All of that works correctly. Problem is when I started this I set the format to be mm/dd/yyyy instead of yyyy/mm/dd. Now that 2016 orders are going in the system it is putting them on top of the 2015 orders. Not sure what my options are but I have about 40,000 records to deal with here.

Any ideas?
Robert FrancisDirector of Continuous ImprovementAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
don't store dates as text, create a new column and place the dates into that.

for output you can then display the dates in any format you need and if really needed could be used in a mixed data type display as you have it now.

e.g.
case when [datecolumn] is null then [sometext] else format( [datecolumn] , 'MM/dd/yyy') end
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
what's your current table design?

My suggestion is:
1. as Paul already mentioned, save the relevant data, and the "date" into another column with Date data type.
2. do NOT save extra/unnecessary data into your table. hence, depends on how we normalise your database design, a new table could be needed here.

>>Now that 2016 orders are going in the system it is putting them on top of the 2015 orders.
this could be your SQL issue, you need to make filtering in selection criteria so that only data needed are selected.
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
For starters this is not my table. We use an application called Jobboss that stores its data in SQL Server. I never modify the existing tables as I don't want things messed up during an upgrade nor do I want to break the system in any way.

That being said we created somewhat of a scheduling system using an existing note section in the application. It was the only available spot to stick a date. That note section uses the note_text column of the job_operation table. I can't change the data type of this column because we still need to be able to put text in it. Trust me when I say if I had created this table the data type would have been date.

I then create a view from the table and create an extra column using the code:

ISNULL(SUBSTRING(dbo.Job_Operation.Note_Text, 11, 15), 'No Date') AS Expr2

Open in new window


Now I did this back in February not thinking about when the new year comes it would mess up the order of the information when sorted alphabetically. I should have used year first then month.

Also to avoid any more confusion, the information they type in the text box is formatted like so:

09/16/2015 1530

Obviously from this point forward I could have them start typing it in:

2015/09/16 1530

but I have 40,000 records from the past using the old format. Doesn't seem like that is going to work.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

PortletPaulEE Topic AdvisorCommented:
OK, thanks for the explanation, you don't have many options.

The only option to "fix" your sorting problems I can see are

1. use a case expression (more on this below)
2. update the data so you have YYYY-MM-DD consistently

Option 2 won't be simple given you are playing with an OOTB production system.

So, option 1 would work along these lines

SELECT
      *
FROM dbo.Job_Operation
      CROSS APPLY (
            SELECT
                  ISNULL(SUBSTRING(dbo.Job_Operation.Note_Text, 11, 15), 'No Date') AS Expr2
      ) AS ca
ORDER BY
      CASE
            WHEN [Expr2] LIKE '%/2015%' THEN 2015
            WHEN [Expr2] LIKE '%/2016%' OR [Expr2] LIKE '%2016/%' THEN 2016
            ELSE 0 -- or maybe 9999 ?
      END
    , [Expr2]


e.g.
    CREATE TABLE Job_Operation
        ([Note_Text] varchar(150))
    ;
        
    INSERT INTO Job_Operation
        ([Note_Text])
    VALUES
        ('10/27/2015 1530'),
        ('10/16/2015 1530'),
        ('10/19/2015 1530'),
        ('10/23/2015 1530'),
        ('10/27/2015 1530'),
        ('**Stocking Agre'),
        ('**Stocking Agre'),
        ('No Date'),
        ('11/04/2015 1530'),
        ('11/04/2015 1530'),
        ('11/05/2015 1530'),
        ('09/16/2015 1530'),
        ('09/16/2015 1530'),
        ('01/16/2016 1530'),
        ('01/16/2016 1530')
    ;
    
**Query 1**:

    SELECT
          *
    FROM dbo.Job_Operation
          CROSS APPLY (
                SELECT
                      -- ISNULL(SUBSTRING(dbo.Job_Operation.Note_Text, 11, 15), 'No Date') AS Expr2
                      Note_Text as Expr2
          ) AS ca
    ORDER BY
          CASE
                WHEN [Expr2] LIKE '%/2015%' THEN 2015
                WHEN [Expr2] LIKE '%/2016%' OR [Expr2] LIKE '%2016/%' THEN 2016
                ELSE 0 -- or maybe 9999 ? 
          END
        , [Expr2]

**[Results][2]**:
    |       Note_Text |           Expr2 |
    |-----------------|-----------------|
    | **Stocking Agre | **Stocking Agre |
    | **Stocking Agre | **Stocking Agre |
    |         No Date |         No Date |
    | 09/16/2015 1530 | 09/16/2015 1530 |
    | 09/16/2015 1530 | 09/16/2015 1530 |
    | 10/16/2015 1530 | 10/16/2015 1530 |
    | 10/19/2015 1530 | 10/19/2015 1530 |
    | 10/23/2015 1530 | 10/23/2015 1530 |
    | 10/27/2015 1530 | 10/27/2015 1530 |
    | 10/27/2015 1530 | 10/27/2015 1530 |
    | 11/04/2015 1530 | 11/04/2015 1530 |
    | 11/04/2015 1530 | 11/04/2015 1530 |
    | 11/05/2015 1530 | 11/05/2015 1530 |
    | 01/16/2016 1530 | 01/16/2016 1530 |
    | 01/16/2016 1530 | 01/16/2016 1530 |

  [1]: http://sqlfiddle.com/#!3/d8889/2

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can't you just filter out all records that aren't from the current year?
Robert FrancisDirector of Continuous ImprovementAuthor Commented:
Paul - I am just now seeing your solution. I will work on this in the next couple of days. Thanks

Vitor - They need to be able to see jobs scheduled both this year and the beginning of next year.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.