Getting Friday week-ending date when date value is a Friday

Database
Enthusiast

Getting Friday week-ending date when date value is a Friday

I am trying to calculate a Friday week-ending date for a given date value in my data.  If the date value is actually a Friday, then that date value's date should be the result.  Every other value should be set to the next Friday date.  I currently have the following code:

 

CAST(Next_Day(date_value, 'FRIDAY') AS TIMESTAMP (0)) AS WeekEndingDate,

 

The problem I am having is that if the date_value date is actually a Friday, then the WeekEndingDate is being set to the next Friday.  I hope there is a way to change this so that if the date_value is Friday, then that same date is the correct week-ending.

 

Thank you!

 

 


Accepted Solutions
Teradata Employee

Re: Getting Friday week-ending date when date value is a Friday

CAST(Next_Day(CAST(date_value AS DATE) - 1, 'FRIDAY') AS TIMESTAMP (0)) AS WeekEndingDate

1 ACCEPTED SOLUTION
5 REPLIES
Junior Contributor

Re: Getting Friday week-ending date when date value is a Friday

Very easy:

Next_Day(date_value -1, 'FRIDAY')

 

Teradata Employee

Re: Getting Friday week-ending date when date value is a Friday

Use date_value-1 instead of date_value.

Enthusiast

Re: Getting Friday week-ending date when date value is a Friday

Thank you for the quick response!  I tried changing to date_value -1, but I get an error stating "Invalid operation for DateTime or Interval".  I reverted back to the code without the "-1" and the error goes away.

 

Rick

Teradata Employee

Re: Getting Friday week-ending date when date value is a Friday

CAST(Next_Day(CAST(date_value AS DATE) - 1, 'FRIDAY') AS TIMESTAMP (0)) AS WeekEndingDate

Junior Contributor

Re: Getting Friday week-ending date when date value is a Friday

When you talk about dates one might assume your datatype is a date :)

 

Another way:

Next_Day(date_value - interval '1' day, 'FRIDAY')