How to select Max date that is valid?

All we need is an easy explanation of the problem, so here it is.

I have a date as varchar (dont ask why). I need to select Max(date). However, date can be faulty, like 2034-34-34. I would like to select max date that is valid and not greater than today. It’s t-sql.

I tried:

create table #tmp (
  dt varchar(10)
)

insert into #tmp values ('2022-02-15'),('2034-34-34')
select max(dt) from #tmp
having isdate(max(dt))=1 and max(dt)<=current_timestamp

As return I get nothing.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

May or may not be an issue, but neither max(dt) nor top 1 ... order by dt desc will return correct result if someone stored a valid date as example: ’04/15/2030′

create table #tmp (
  dt varchar(10)
);

insert into #tmp values ('2022-02-15'),('2034-34-34'),('04/15/2030');

select max(dt) from #tmp
where isdate(dt)=1;

2022-02-15

Reason is that dt is compared as a string. You can cast valid "dates" to dates, before comparing:

select max(cast(dt as date)) from #tmp
where isdate(dt)=1;

2030-04-15

Fiddle

Method 2

Your HAVING clause was the issue in your post’s example query. HAVING is usually used to filter with aggregates within a group of values. Also your were evaluating if the max value was a date in the order of events in that HAVING clause, rather than filtering out non-date values first, then getting the max, such as your answer now does.

Another version of the query to get you the same answer would just be:

create table #tmp (
 dt varchar(20)
)

insert into #tmp values ('2022-02-15'),('2034-34-34'),('2022-03-14')
select max(convert(date, dt)) from #tmp
where isdate(dt)=1

This could potentially be faster by eliminating the use of an ORDER BY clause, especially if you indexed your table by the field dt like so CREATE CLUSTERED INDEX IX_TMP_DT ON #tmp (dt). This would store the data already sorted by the dt column then, which is beneficial if you were to have repeated runs of the SELECT query. One could verify the differences by looking at the execution plans, and time & I/O statistics.

Method 3

This is what I have for now:

create table #tmp (
 dt varchar(20)
)

insert into #tmp values ('2022-02-15'),('2034-34-34'),('2022-03-14'),(null)
select top 1 dt from #tmp
where isdate(dt)=1
order by dt desc

The result:
2022-03-14

Update:
Thank you all, I have a solution from your answers:

create table #tmp (
 dt varchar(20)
)

insert into #tmp values 
    ('2023-02-15'),
    ('2034-34-34'),
    ('2022-03-14'),
    ('2000-01-01'),
    (null),
    ('04/14/2030')

select 
  max(try_cast(dt as date)) as maxValidDate
from 
  #tmp
where 
  try_cast(dt as date ) is not null and try_cast(dt as date) <= current_timestamp

Returns 2022-03-14

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply