Dynamic Order by -need both Dates and Varchars

  • 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

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • 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

  • 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[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • So how do I get it out of here

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply