All Forums Database
Mike 7 posts Joined 08/05
27 Apr 2006
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

Nytewynd 13 posts Joined 12/05
28 Apr 2006

select calendar_datefrom sys_calendar.calendarwhere day_of_week = 6 and calendar_date between date -6 and date

Ashok.Pentapati 16 posts Joined 06/09
31 May 2012

You can give a try using:

Select day_of_week(date_column1) from Table1;

 

Thanks,

CarlosAL 512 posts Joined 04/08
01 Jun 2012

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

You must sign in to leave a comment.