Order by date desc in Varchar Column 1

[Autosys]

Hi there

I have a column (varchar(255). This column contains either dates in the format dd/mm/yyyy, the string "TBD" or the string "Abandoned".

I was wondering if there was a way to select all the data in this column but order the data so that the all the rows with dates are displayed first (in a date descending order) followed by the rest of the rows containing the strings TBD or Abandoned.

Any help would be greatly appreciated thanks.

 

[bborissov]

Is this what you want:

DECLARE @Test       TABLE (Test varchar(255))
INSERT INTO @Test VALUES ('TBD')
INSERT INTO @Test VALUES ('Abandoned')
INSERT INTO @Test VALUES ('21/01/2008')
INSERT INTO @Test VALUES ('21/03/2008')
INSERT INTO @Test VALUES ('21/04/2008')

SELECT * FROM @Test
ORDER BY CASE WHEN Test = 'TBD'
                   THEN 2
              WHEN Test = 'Abandoned'
                   THEN 3
         ELSE 1 END, Test DESC

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Autosys]

cheers bborissov

almost something like that yes ..

you query looks good however the date is not sorted/ordered properly ....

as the column is not of datatype Date, the query does not seem to know how to sort the strings that are in a date format. It does something like this:

29/08/2008
28/08/2008
27/08/2008
26/07/2008
25/09/2008
29/08/2008
Abandoned

etc etc.

so it's almost as if the strings that look like dates need to be converted into dates first?

thanks for the help.

 

[bborissov]

That is why you always should save the values in the proper fields.

-- That is important!
SET DATEFORMAT DMY

DECLARE @Test       TABLE (Test varchar(255))
INSERT INTO @Test VALUES ('TBD')
INSERT INTO @Test VALUES ('Abandoned')
INSERT INTO @Test VALUES ('29/08/2008')
INSERT INTO @Test VALUES ('28/08/2008')
INSERT INTO @Test VALUES ('27/08/2008')
INSERT INTO @Test VALUES ('26/07/2008')
INSERT INTO @Test VALUES ('25/09/2008')
INSERT INTO @Test VALUES ('29/08/2008')

SELECT Test,
       CASE WHEN IsDate(Test)=1
                 THEN CAST(Test AS DateTime)
            WHEN Test = 'TBD'
                 THEN CAST(1 AS datetime)
            ELSE CAST(0 AS datetime) END AS OrdBy
FROM @Test
ORDER BY OrdBy DESC

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[bborissov]

And if you want to get rid of the additional column, use that query as a derived table:

SET DATEFORMAT DMY

DECLARE @Test       TABLE (Test varchar(255))
INSERT INTO @Test VALUES ('TBD')
INSERT INTO @Test VALUES ('Abandoned')
INSERT INTO @Test VALUES ('29/08/2008')
INSERT INTO @Test VALUES ('28/08/2008')
INSERT INTO @Test VALUES ('27/08/2008')
INSERT INTO @Test VALUES ('26/07/2008')
INSERT INTO @Test VALUES ('25/09/2008')
INSERT INTO @Test VALUES ('29/08/2008')
SELECT Test
  FROM(SELECT Test,
              CASE WHEN IsDate(Test)=1
                        THEN CAST(Test AS DateTime)
                   WHEN Test = 'TBD'
                        THEN CAST(1 AS datetime)
              ELSE CAST(0 AS datetime) END AS OrdBy
       FROM @Test) Tst
ORDER BY OrdBy DESC

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Autosys]

thanks bborissov will try it out again tomorrow once i have access to this system again.

A

 

[Autosys]

Hi bborissov

The query works for all the values in the column that are dates until it finds the first non-date value (TBD, Abandoned etc) ... after that the OrdBy column displays 1900-01-01 00:00:00:000 for all the other columns inc. the ones that do have actual dates in for values.

thansk for the help ...

 

[bborissov]

I don't get it?
I thought you wanted Date first and then all other records w/o care how they are sorted?
Don't look in the OrdBy column. It is only help column to do order. So how you want other fields to be sorted?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Autosys]

Hi bborissov

Yep I don't care how the other fields are sorted .. but at the moment .. if the column looks like this:

mm-dd-yyyy
mm-dd-yyyy
mm-dd-yyyy
mm-dd-yyyy
TBD
Abandoned
Abandoned
mm-dd-yyyy
mm-dd-yyyy
mm-dd-yyyy

then only the top 4 dates get sorted ... until the query reaches the first TBD/Abandoned.

Actually ... by looking into it further now .. another problem could be because the default date in sql is set to: yyyy-mm-dd .... and the dates stored in the column is in the format dd-mm-yyyy

So maybe it gets a bit confused. The change has actually now been made in the Front End to display the results as desired ... which means it doesn't need to be fixed anymore asap but I will carry on playing around with it as I'm keen to understand how it works.

Thanks for all your help!

 

[bborissov]

In both my posts I have:

SET DATEFORMAT DMY

before the query use it.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Autosys]

my mistake sorry! works a treat now!

the latest date gets displayed first ... is there a way to display the oldest date first instead but still keep the dates at the top?

thanks again for all your help!

 

[bborissov]

SET DATEFORMAT DMY

DECLARE @Test       TABLE (Test varchar(255))
INSERT INTO @Test VALUES ('TBD')
INSERT INTO @Test VALUES ('Abandoned')
INSERT INTO @Test VALUES ('29/08/2008')
INSERT INTO @Test VALUES ('28/08/2008')
INSERT INTO @Test VALUES ('27/08/2008')
INSERT INTO @Test VALUES ('26/07/2008')
INSERT INTO @Test VALUES ('25/09/2008')
INSERT INTO @Test VALUES ('29/08/2008')
SELECT Test
  FROM(SELECT Test,
              CASE WHEN IsDate(Test)=1
                        THEN CAST(Test AS DateTime)
                   WHEN Test = 'TBD'
                        THEN CAST(1 AS datetime)
              ELSE CAST(0 AS datetime) END AS OrdBy,
              CASE WHEN IsDate(Test)=1
                        THEN 1
                   WHEN Test = 'TBD'
                        THEN 2
              ELSE 3 END AS OrdBy2
       FROM @Test) Tst
ORDER BY OrdBy2, OrdBy

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[Autosys]

fantastic thanks for all your help bborissov