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.
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.
HAVINGclause was the issue in your post's example query.
HAVINGis 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
HAVINGclause, 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
This could potentially be faster by eliminating the use of an
ORDER BYclause, especially if you indexed your table by the field
CREATE CLUSTERED INDEX IX_TMP_DT ON #tmp (dt). This would store the data already sorted by the
dtcolumn then, which is beneficial if you were to have repeated runs of the
SELECTquery. One could verify the differences by looking at the execution plans, and time & I/O statistics.