CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Alias
Alias

Reputation: 413

Select data in date format

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?

Upvotes: 1

Views: 1177

Answers (3)

OzrenTkalcecKrznaric
OzrenTkalcecKrznaric

Reputation: 5646

Another solution would be using PATINDEX in WHERE clause.

SELECT PATINDEX('[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]', field_value)

Problem with this approach is you really are not sure if something is date (e.g. 99/99/9999 is not date). And problem with IS_DATE is it depends on configuration (e.g. DATEFORMAT). So, use an appropriate option.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270081

To do this without a conversion error:

select max(case when isdate(col) = 1 then cast(col as date) end)  -- or use convert()
from . . .

The SQL statement does not specify the order of operations. So, even including a where clause in a subquery will not guarantee that only dates get converted. In fact, the SQL Server optimizer is "smart" enough to do the conversion when the data is brought in and then do the filtering afterwards.

The only operation that guarantees sequencing of operations is the case statement, and there are even exceptions to that.

Upvotes: 0

Yuck
Yuck

Reputation: 50855

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.

Upvotes: 2

Related Questions