Dynamic Order by -need both Dates and Varchars

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Dynamic Order by -need both Dates and...

Dynamic Order by -need both Dates and Varchars

Rate Topic

Display Mode

Topic Options

Author

Message

bboufford

Posted Thursday, February 04, 2010 11:01 AM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 04, 2010 7:37 AM
Points: 17, Visits: 45

I am using a dynamic order by in a SSRS Report

(note this is not a working query for example only)
Select No.
Description
Title
Author
Published Date
Mfg Date
from all of my tables
order by Order by Case @orderby when 'Job' then Job.[No_]
when 'Author' then Job.[Description 2]
WHEN 'MSDue' then CASE Job.[MFG Date] WHEN '1753-01-01 00:00:00.000' Then '12-31-2049'
ELSE Job.[MS Due Date]
END

I know I am supposed to use only Text or Dates but when I convert my dates to text they don't sort correctly. Can anyone help me with this conundrum. Thank you

Barb

Post #859818

LutzM

Posted Thursday, February 04, 2010 12:15 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

You need to convert your date values to varchar using CONVERT() function.
You could use CONVERT(VARCHAR(19),Job.[MS Due Date],120) to get a date format which can be sorted.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function

Post #859860

bboufford

Posted Thursday, February 04, 2010 12:34 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 04, 2010 7:37 AM
Points: 17, Visits: 45

I do not get any sorting happening at all when I use your suggestion - any more suggestions
Wink

Thanks

WHEN 'MSDue' then CASE CONVERT(VARCHAR(19),Job.[MS Due Date],120) WHEN '1753-01-01 00:00:00.000' Then '12-31-2049'
ELSE CONVERT(VARCHAR(19),Job.[MS Due Date],120)
End

Post #859873

LutzM

Posted Thursday, February 04, 2010 1:21 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131

No. Not in this thread, since I don't think there should be a parallel discussion.

Post #859930

bboufford

Posted Thursday, February 04, 2010 1:30 PM

Grasshopper

Group: General Forum Members
Last Login: Friday, June 04, 2010 7:37 AM
Points: 17, Visits: 45

So how do I get it out of here

Post #859937

« Prev Topic | Next Topic »

Permissions