This is how example 2 can be reworked with a CASE function in the ORDER BY clause that properly sorts by the datetime field:

DECLARE @column varchar(10)

SET @Column = 'title'

SELECT EmployeeID,

CASE @column

WHEN 'Name' THEN LastName

WHEN 'Title' THEN Title

ELSE LastName

END AS Column1,

CASE @column

WHEN 'Name' THEN FirstName

WHEN 'Title' THEN LastName

ELSE CAST(BirthDate as varchar(20))

END AS Column2,

CASE @column

WHEN 'Title' THEN CAST(HireDate as varchar(20))

ELSE ''

END AS Column3, HireDate

FROM employees

ORDER BY CASE @column WHEN 'Title' THEN

LTRIM(STR(DATEPART(yyyy,HireDate)))

+ CASE LEN(LTRIM(STR(DATEPART(mm,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(mm,HireDate)))

ELSE LTRIM(STR(DATEPART(mm,HireDate))) END

+ CASE LEN(LTRIM(STR(DATEPART(dd,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(dd,HireDate)))

ELSE LTRIM(STR(DATEPART(dd,HireDate))) END

WHEN 'Name' THEN FirstName END

The could you would need to adapt to your datetime columns is this:

LTRIM(STR(DATEPART(yyyy,HireDate)))

+ CASE LEN(LTRIM(STR(DATEPART(mm,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(mm,HireDate)))

ELSE LTRIM(STR(DATEPART(mm,HireDate))) END

+ CASE LEN(LTRIM(STR(DATEPART(dd,HireDate)))) WHEN 1 THEN '0' + LTRIM(STR(DATEPART(dd,HireDate)))

ELSE LTRIM(STR(DATEPART(dd,HireDate))) END

This would come after the THEN keyword, simply change the column name to the one you plan to use.

Robert Marda