Working With Dates

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Working With Dates

Hi,

 

I want to see data for previous day in our reports.

Thus all I am doing is  : Sel * from table where date_col= Current_Date-1.

 

However when it a Monday , it has to extarct the data for the last Friday.

Thus Current_Date-1 will not work here.

Any optimized way to perform this , experts?

 

Regards

Swapnanil

3 REPLIES
Teradata Employee

Re: Working With Dates

Hi.

 

CASE WHEN is your friend.

 

SELECT *

  FROM Table

 WHERE date_col = CURRENT_DATE - (CASE WHEN ((CURRENT_DATE - DATE '1900-01-01') MOD 7) + 1 <> 1 THEN 1 ELSE 3 END)

 

Cheers.

 

Carlos.

Teradata Employee

Re: Working With Dates

Another solution with the same idea behind Carlos' solution :

select *
  from Table
 where date_col = current_date - case current_date when trunc(current_date, 'iw') then 3 else 1 end;
Enthusiast

Re: Working With Dates

Thanks guys!!
Looks a lot neat. I used join with SysCalender table and looked a bit busy the query!!!