Populating column with constant day-of-week value?

Database
Enthusiast

Populating column with constant day-of-week value?

Hello,

Here's something I've been wrestling with: I need a column of data that will indicate that the data was refreshed on a particular day of the week. Most likely it was be the past Friday's date. Thus, I need to populate the column with lat Friday's date, no matter which day the query is run.

Any ideas?

Thanks,

Mike
3 REPLIES
Enthusiast

Re: Populating column with constant day-of-week value?


select calendar_date
from sys_calendar.calendar
where day_of_week = 6
and calendar_date between date -6 and date

Enthusiast

Re: Populating column with constant day-of-week value?

You can give a try using:

Select day_of_week(date_column1) from Table1;

Thanks,

Junior Supporter

Re: Populating column with constant day-of-week value?

Hi:

I think this may work for you:

select whatever_date, (whatever_date) - ((whatever_date - date '1900-01-05') mod 7) "Last Friday"

Examples:

select whatever_date, (whatever_date) - ((whatever_date - date '1900-01-05') mod 7) "Last Friday" FROM (SELECT DATE '2012-06-01' whatever_date ) pre;

 *** Query completed. One row found. 2 columns returned.

 *** Total elapsed time was 1 second.

whatever_date  Last Friday

-------------  -----------

   2012-06-01   2012-06-01

 BTEQ -- Enter your SQL request or BTEQ command:

select whatever_date, (whatever_date) - ((whatever_date - date '1900-01-05') mod 7) "Last Friday" FROM (SELECT DATE '2012-05-30' whatever_date ) pre;

 *** Query completed. One row found. 2 columns returned.

 *** Total elapsed time was 1 second.

whatever_date  Last Friday

-------------  -----------

   2012-05-30   2012-05-25

 BTEQ -- Enter your SQL request or BTEQ command:

select whatever_date, (whatever_date) - ((whatever_date - date '1900-01-05') mod 7) "Last Friday" FROM (SELECT DATE '2012-05-15' whatever_date ) pre;

 *** Query completed. One row found. 2 columns returned.

 *** Total elapsed time was 1 second.

whatever_date  Last Friday

-------------  -----------

   2012-05-15   2012-05-11

HTH.

Cheers.

Carlos