Select data in date format

advertisements

I have a query in which I want to select data from a column where the data is a date. The problem is that the data is a mix of text and dates.

This bit of SQL only returns the longest text field:

SELECT MAX(field_value)

Where the date does occur, it is always in the format xx/xx/xxxx

I'm trying to select the most recent date.

I'm using MS SQL.

Can anyone help?


Try this using ISDATE and CONVERT:

SELECT MAX(CONVERT(DateTime, MaybeDate))
FROM (
  SELECT MaybeDate
  FROM MyTable
  WHERE ISDATE(MaybeDate) = 1) T

You could also use MAX(CAST(MaybeDate AS DateTime)). I got in the (maybe bad?) habit of using CONVERT years ago and have stuck with it.