How to select Max date that is valid?



  • 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.



  • 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2