Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic Order by -need both Dates and Varchars Expand / Collapse
Author
Message
Posted Thursday, February 4, 2010 11:01 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 4, 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
Posted Thursday, February 4, 2010 12:15 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 7,147, Visits: 13,213
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
Posted Thursday, February 4, 2010 12:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 4, 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
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
Posted Thursday, February 4, 2010 1:21 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 11:08 AM
Points: 7,147, Visits: 13,213
No. Not in this thread, since I don't think there should be a parallel discussion.



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 #859930
Posted Thursday, February 4, 2010 1:30 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, June 4, 2010 7:37 AM
Points: 17, Visits: 45
So how do I get it out of here
Post #859937
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse