Using case to ORDER by not working !

• John Burchel

  Hall of Fame

  Points: 3611

  More actions

  August 17, 2006 at 3:53 am

  #62104

  Hi All,

  I am trying to use a case statement to order by, its not working..

  Select top 10 * from #files_on_folder

  order by

  CASE

         WHEN isdate(substring (file_name,patindex('%200%',file_name),8)) = 1 then CONVERT(DATETIME,substring (file_name,patindex('%200%',file_name),8))

         WHEN isdate(substring (file_name,patindex('%200%',file_name),8)) = 0 then file_name

  END

  DESC

  ---

  Kindest Regards,

  John Burchel (Trainee Developer)
  

• David.Poole

  SSC Guru

  Points: 76001

  More actions

  August 17, 2006 at 4:03 am

  #655400

  So you are trying to extract the date from a filename and use that as the sorting key.

  Where a date cannot be found you are just going to sort by the file name.

  I suspect that part of the problem is that when a date is found you convert to a DATETIME but when one is not found you are going to have a VARCHAR.

  I would start by converting the date to a VARCHAR in a format that will be suitable for string sorting i.e. YYYY-MM-DD format.

  LinkedIn Profile