asked on

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?

PortletPaul

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 Chong

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 Francis

ASKER

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

Can't you just filter out all records that aren't from the current year?

Robert Francis

ASKER

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.