I get this error from the following code as last modified is a date., I can
cast it as varchar but then it sorts as text not date. any ideas

Conversion failed when converting datetime from character string


CASE @Direction

WHEN 'DESC' THEN

CASE @OrderBy


WHEN 'FirstName' THEN firstname

WHEN 'TelNo' THEN TelNo

WHEN 'Email' THEN email

WHEN 'FaxNo' THEN FaxNo

WHEN 'LastModified' THEN  LastModified


                                 END

END

  DESC,

CASE @Direction

WHEN 'ASC' THEN

  CASE @OrderBy



WHEN 'FirstName' THEN firstname

  WHEN 'TelNo' THEN TelNo

WHEN 'Email' THEN email

WHEN 'FaxNo' THEN FaxNo

WHEN 'LastModified' THEN  LastModified

END

END

Since the CASE only returns one column, then it can only return one
datatype.  And the datatype is determined by the datatype precedence.
http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx

Looking at that shows that, by default, character strings will be cast into
datetime instead of datetime being cast into a character string.  Although
character strings (char, nchar, varchar, nvarchar) may seem more general,
they are very low in precedence.

What you can do in this case is:

WHEN 'LastModified' THEN  CONVERT (VARCHAR(23), LastModified, 121)

RLF




Show quoteHide quote

As alternative to casting DATETIME to character data type, you can have
additional CASE expressions for each data type.

ORDER BY CASE @Direction
           WHEN 'DESC'
           THEN
             CASE @OrderBy
               WHEN 'FirstName' THEN firstname
               WHEN 'TelNo' THEN TelNo
               WHEN 'Email' THEN email
               WHEN 'FaxNo' THEN FaxNo
             END
          END DESC,
          CASE @Direction
           WHEN 'DESC'
           THEN
             CASE @OrderBy
               WHEN 'LastModified' THEN  LastModified
             END
          END DESC,
          CASE @Direction
           WHEN 'ASC'
           THEN
             CASE @OrderBy
               WHEN 'FirstName' THEN firstname
               WHEN 'TelNo' THEN TelNo
               WHEN 'Email' THEN email
               WHEN 'FaxNo' THEN FaxNo
             END
          END ASC,
          CASE @Direction
           WHEN 'ASC'
           THEN
             CASE @OrderBy
               WHEN 'LastModified' THEN  LastModified
             END
          END ASC;

You can also try dynamic SQL to see if that will perform better:
http://www.sommarskog.se/dynamic_sql.html#Order_by

each potential value returned from your CASE statement must be of the
same type, so you do need to cast LastModified to a varchar. This
shouldn't be a problem - you can specify the format the datetime
should be in, and if you use a format like yyyy-mm-dd hh:mm, then
sorting by the varchar should give you the same results as sorting on
the date.

The 4 guys have a good article about this:
http://www.4guysfromrolla.com/webtech/010704-1.shtml

Testing Environment + TestData + QA Setup
Restoring single filegroup
Migrating SQL2000 Databases to SQL2005
SSMS Sometimes shows keys - sometimes not
64 bit standard edition doesn't use page file
Transaction Log Backup Doesn't Truncate Log
help on tempdb log full
Change autogrowth for a log file SQL 2005
Maintenance Plans
Error: 18456, Severity: 14, State: 10.