date column value

[rajiivnb]

Hi ,

One of my date column value should always display the immeiate past friday.So when ever the job is called the value of the date column should display the immediate past friday date.
Can anyone help me out .

Regards,
Rajii

[rajiivnb]

Hi,
Will PreviousWeekdayFromDate will help me out .

Regards,
Rajiivnb

[chalasaniamith]

One of my date column value should always display the immeiate past friday.So when ever the job is called the value of the date column should display the immediate past friday date.

write a routine for urself so that u will get the immediateast friday
for ex:check the routine rowprocgetpreviousvalue use that and u can write ur own

[ArndW]

I would suggest doing a short DS Function as follows, with the ")DateToCheck being in internal format:

Declaration: LastFriday(DateToCheck)
Code:

Code: Select all

   TodaysDOW   = ICONV(DateToCheck,"MDW")
   DaysToFriday = 5-TodaysDOW
   IF (DaysToFriday => 0) THEN Ans = DateToCheck-(7-DaysToFriday)
                                      ELSE Ans = DateToCheck + DaysToFriday)

[Sainath.Srinivasan]

I believe there needs to be only a '>' symbol and not the '=' symbol as you may consider 'if today being Friday' and is the date you want.

[ArndW]

Srini: No, the "=>" needs to be there in the code, otherwise the logic wouldn't work as Rajiv specified. "last Friday" means the previous Friday, so if today is a Friday then we would need to go back one week. But it's a moot point as I'm sure that Rajiv will know how to fix it if it doesn't function as he expects.

[ArndW]

I just saw that although Rajiv specified "Server Edition" in his post, this thread is in the Px section of the forum. The Uv/Basic solution shown earlier only works in Server or in a Px Basic Transform stage.

[ray.wurlod]

Rajiv's response relies on the fact that in server jobs the internal format is an integer. The day of week can be derived using Mod(intdate, 7). 0 is Sunday (as it happens) and 6 Saturday. Thus 5 is Friday. The rest is purely arithmetic.

[rajiivnb]

Hi Ray,
with the mod function i could be able to try out the day of the week but i wanted to display following friday of the week .

Regards,
Rajiivnb

[ray.wurlod]

As I said, it's just arithmetic.

If today is Wednesday (3), then the Friday of this week is two (5 -3) days on.

Friday of the following week is that plus seven (5-3+7 added to current date).

Friday of the previous week is (7-3+1) days before. Subtract from current date.

Until and unless "they" change the week from containing seven days, this method will work.

[rajiivnb]

Code:

TodaysDOW = ICONV(DateToCheck,"MDW")
DaysToFriday = 5-TodaysDOW
IF (DaysToFriday => 0) THEN Ans = DateToCheck-(7-DaysToFriday)
ELSE Ans = DateToCheck + DaysToFriday)



_________________

Hi ArndW,
Is the DateTocheck available in server jobs,

Regards,
Rajiiv

[ArndW]

Rajiv,

the "DateToCheck" is your input date, put as a parameter to the suggested routine. You can use @TODAY instead, if you always want to check on the date at which the job runs. If you create your own Server routine in the manager and cut-and-paste the code I gave you it will compile and work.

[rajiivnb]

Hi ArndW,
Sorry for troubling you much .This is the code which i given in server routine
DefFun LastFriday(DateToCheck)
TodaysDOW = ICONV(DateToCheck,"MDW")
DaysToFriday = 5-TodaysDOW
IF (DaysToFriday > 0) THEN Ans = DateToCheck-(DaysToFriday+7)
ELSE Ans = DateToCheck + DaysToFriday


and it compiles and gives me a result as -12 for todays date

[ArndW]

Hello Rajiv,

let's start at the beginning with a step-by-step procedure:

1. In the Manager, "New Server Routine" option.
2. Create "LastFriday" with one parameter, "DateToCheck"
3. Put in the following code:

Code: Select all

IF NOT(TRIM(DateToCheck)) THEN WorkDate = @DATE
                          ELSE WorkDate = DateToCheck

TodaysDOW    = OCONV(WorkDate,"DW") 
DaysToFriday = 5 - TodaysDOW 
IF (DaysToFriday > 0) THEN Ans = WorkDate + DaysToFriday - 7 
                      ELSE Ans = WorkDate + DaysToFriday 

Note - the Input & Output dates are in DataStage internal (Julian) format.

[rajiivnb]

Hi ArndW,
Thanks for all your kind help.But will this routine take care of feb dates also in leap year.

Regards,
Rajiiv