SQL Server 条件排序依据

Question

我在 SQL Server 2005 中有一个 SQL 查询，当我包含条件排序依据时，该查询会中断。当我删除 order by 时，查询有效。当我明确地编写按条件排序(例如按 p.Description 排序)时，它会起作用。当我包含条件排序依据时，出现错误，

'Conversion failed when converting character string to smalldatetime data type'

SQL Server 没有向我显示哪一行代码导致了此错误。我想知道如何解决此问题，以便我可以使用条件排序依据或对转换中失败的列进行故障排除。

declare @SearchTerm nvarchar(255)
declare @SortBy nvarchar(255)
declare @Months int
declare @VendorID int
declare @ProductID int

set @SearchTerm = 'focus'
set @SortBy = 'product'
set @Months = 3
set @VendorID = null
set @ProductID = null

-- This makes it so the @Month will filter by n number of months ago.
declare @PreviousMonths datetime
if @Months is null
    begin
        set @PreviousMonths = 24
    end
else
    begin
        set @PreviousMonths = DateAdd(month, -@Months, GetDate())
    end

select
    a.dsAlertID as AlertID,
    a.ProductID,
    v.VendorID,
    p.Description as ProductName,
    v.LongName as VendorName,
    a.Introduction,
    a.Writeup,
    a.DateAdded 
from
    ev_ds_Alerts a
left outer join
    tblProducts p on a.ProductID = p.ProductID
left outer join
    tblVendors v on v.VendorID = p.VendorID
where
    ( @SearchTerm is null or ( a.Writeup like '% ' + @SearchTerm + '%' or a.Introduction like '% ' + @SearchTerm + '%') )
    and (( @Months is null ) or ( @Months is not null and a.DateAdded >= @PreviousMonths))
    and (( @VendorID is null ) or ( @VendorID is not null and v.VendorID = @VendorID ))
    and (( @ProductID is null ) or ( @ProductID is not null and p.ProductID = @ProductID ))
order by
    case @SortBy
        when 'product' then p.Description
        when 'vendor' then v.LongName
        else a.DateAdded
    end

-- order by p.Description or v.LongName works when explicitly writing them out!

Answer 1

根据之前的答案，尝试:

order by
    case @SortBy
        when 'product' then p.Description
        when 'vendor' then v.LongName
        else convert(VARCHAR(25),a.DateAdded,20)

这应该为您提供所需的排序，因为它将格式化日期字符串 yyyy-mm-dd hh:mm:ss。