Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2002
    Location
    Sunnyvale, CA USA
    Posts
    78

    Unanswered: How do I make a date sort-order dependent on a variable?

    In a TSQL Stored Proc I want to make the sort-order of a date field dependent on a variable. This is easy to if it's an integer field (a case statement with negative result to get the descending version), but I can't think how to do it for a date. For example, this is what I'd like to do:-

    Code:
    declare @SortOrder int
    set @SortOrder = 0
    
    select
    	myDateField
    from (
    	select dateadd(dd,1,getdate()) myDateField
    	union all
    	select dateadd(dd,2,getdate())
    	union all
    	select dateadd(dd,3,getdate())
    ) simulatedData
    order by
    	case
    	when @SortOrder=1
    	then myDateField	-- Ascending
    	else -1*myDateField	-- Descending
    	end
    Unfortunately "*-1*myDateField" is not legal. Any suggestions?
    I'd really like this to be in one statement.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    You could order using the DATEDIFF function relative to some arbitrary date. How wide is your range of dates?

    Code:
    declare @SortOrder int
    set @SortOrder = 0
    
    select create_date
    from sys.objects
    order by @SortOrder * datediff(ss, create_date, getdate())

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •