CODEWITHSUNDEEP
sqlsql-servert-sql
afk
afk

Reputation: 43

Retrieving max date from multiple columns, for dates before today

I've written some code to extract the latest date from multiple columns.

select (select max(LatestDate)
        from (values (col1),(col2),(col3)) as updatedate(LatestDate)
        ) as LatestDate

from table1

However, I only want to take the date if it's before today. When I run the code for the sample dates below, it gives me the latest date as 10/04/2019 which is after today.

The date that i'd want it to extract is 14/03/2019 (col2) as it's before today, and is the latest date of all the columns whose date is before today.

Today = 27/03/2019
col1 = 02/02/2019
col2 = 14/03/2019
col3 = 10/04/2019

Can anyone advise on this? Hope it makes sense.

Many thanks

afk

Upvotes: 1

Views: 3301

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1271061

Add a where clause. I would phrase the query using cross apply:

select max(LatestDate)
from table1 t1 cross apply
     (values (t1.col1), (t1.col2), (t1.col3)
     ) updatedate(LatestDate)
where updateddate.LatestDate < getdate();

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use APPLY with WHERE clause :

select t.*, tt.LatestDate
from table1 t outer apply
     ( select max(LatestDate) as LatestDate
       from ( values (col1),(col2),(col3) ) as updatedate(LatestDate)
       where LatestDate < convert(date, GETDATE())
     ) tt;

Upvotes: 1

Thermos
Thermos

Reputation: 181

Try this - you can take the MAX date that is less than or equal to today:

with cte as 
(
  select * 
  from (values (col1),(col2),(col3)) as updatedate
)
select (
         select max(updatedate)
         from cte
         where updatedate <= GETDATE()
        ) as LatestDate
from table1

Upvotes: 0

Dheerendra
Dheerendra

Reputation: 308

You can use the below code for achieving the same.

select (select max(LatestDate)
        from (values (col1),(col2),(col3)) as updatedate(LatestDate)
        where updatedate < CAST(GETDATE() AS DATE) 
        ) as LatestDate

from table1

Upvotes: 0

Related Questions