Previous Weekday From Date

gdean · Post by **gdean** » Mon Jun 28, 2004 3:08 pm

Hi all,

I am trying to find what was the previous weekday from a given date. For example given today's date, I need to find what was the date on last friday. One crude way I could think of is to find the weekday of the given date and then subtract the respective number of days to get to last friday's date. But I'm sure there is a better way to do this.

I found a function "PreviousWeekdayFromDate" in DataStage developers help guide which is for the same purpose. But this is available only in Parallel jobs. Can someone give me a clue on how to achieve this functionality in Server jobs??

Thank you,
Gregg

ray.wurlod · Post by **ray.wurlod** » Mon Jun 28, 2004 4:00 pm

The following assumes the argument is an internal format date.

Code: Select all

FUNCTION PreviousWeekdayFromDate(TheDate)
RoutineName = "PreviousWeekdayFromDate"

* Oconv with "DWA" as the second argument returns the full weekday name, 
* "DWB" the abbreviated weekday name, 
* "DW" the ordinal day number in the week.

Ans = Oconv(TheDate, "DWA")

If Status() <> 0
Then
   Ans = @NULL
   Call DSTransformError("Invalid date " : Quote(TheDate), RoutineName)
End

RETURN(Ans)

gdean · Post by **gdean** » Mon Jun 28, 2004 7:31 pm

Hi Ray,

Thank you very much for your response. I'm sorry if I was not clear in stating my motive. I am using Oconv(TheDate, "DWA") to know the weekday.

For eg:

Code: Select all

Oconv(Iconv("2004-06-28","D-YMD"), "DWA") gives me Monday.

But my requirement is when the day is "Monday" or "Tuesday" or "Wednesday" I have to replace a date column with the date of the previous "Friday"

So, if my input date is "2004-06-28" I should get an output of "2004-06-25"

Here is an excerpt from the DataStage help file that exactly states what I'm trying for:

Function Name:PreviousWeekdayFromDate
Description:Returns the date of the specified day of the week most recent before the source date
Arguments:source_date day_of_week (string)
Output:date

Please correct me if I'm not understanding your point. I really appreciate your help.

Thanks,
Gregg

ray.wurlod · Post by **ray.wurlod** » Mon Jun 28, 2004 11:50 pm

The quick, but not optimal solution, is to loop backwards until the required day is found.

Code: Select all

FUNCTION PreviousWeekdayFromDate(TheDate, WeekDay)
aTheDate = TheDate
aWeekDay = Upcase(WeekDay)
Loop
   aTheDate -= 1
While Oconv(aTheDate, "DWA":@VM:"MCU") <> aWeekDay
Repeat
Ans = aTheDate
RETURN(Ans)

There is a more efficient, arithmetic, solution, but I'm really busy now. Just note that Mod(TheDate,7) returns a value between 0 and 6; you can work with this.

gdean · Post by **gdean** » Wed Jun 30, 2004 7:42 am

Thank you Ray. That worked perfectly.

-Gregg

ray.wurlod · Post by **ray.wurlod** » Wed Jun 30, 2004 6:25 pm

This one may be faster.

Code: Select all

FUNCTION PreviousWeekdayToDate(TheDate, DayName)
* TheDate is expected to be internal date format.

* By using EQUATE, concatenation is performed at compile time.
Equate DayList To "SUNDAY" : @FM : "MONDAY" : @FM : "TUESDAY" : @FM : "WEDNESDAY" : @FM : "THURSDAY" : @FM : "FRIDAY" : @FM : "SATURDAY"
Equate RoutineName To "PreviousWeekdayToDate"

Ans = @NULL

Locate UpCase(DayName) In DayList Setting Pos
Then
   DayNumber = Iconv(Oconv(TheDate, "D"), "DW")
   If Status() = 0
   Then
       Pos -= 1   ; * map 0-6 to 1-7
       Offset = Pos - DayNumber - (7 * (Pos >= DayNumber))
       Ans = TheDate + Offset
   End
   Else
      Call DSTransformError("Unable to resolve day number from " : Quote(TheDate), RoutineName)
   End
End
Else
   Call DSTransformError("Day name " : Quote(DayName) : " not recognized.", RoutineName)
End

RETURN(Ans)

hexa · Post by **hexa** » Fri Nov 30, 2007 2:46 am

Pls. can I get more detail on this "Previous Weekday From Date Function".

Thanks

Hexa

ray.wurlod · Post by **ray.wurlod** » Fri Nov 30, 2007 2:50 am

No, because all the detail there will ever be is in this thread.

For less than 30c per day you can purchase premium membership which, among other things, allows you to read the entirety of posts by the five premium posters. Premium membership is 100% allocated to the hosting and bandwidth costs incurred by DSXchange; the premium posters are, like all other posters, unpaid.

hexa · Post by **hexa** » Fri Nov 30, 2007 3:00 am

Thnks for your kind reply..

I am also facing Similar kind of issues. How to get Prvious weekday from day.

I am lucky if I get help on this.

Hexa

NickH · Post by **NickH** » Fri Nov 30, 2007 6:42 am

Hi,
Probably not as nice a solution as Ray's but the following will do what you want

Create a Function

DateOfMostRecentWeekDay(DateIn,DayOfWeek)

*****************************************************
*
* Will give the date of the most recent weekday to the date
* ie if Friday and current date is 28/12/07 then return will be
* 28/12/07.
*
* DateIn is in the form YYYY-MM-DD
*
* DayOfWeek is numeric as per table below this is for non NLS enabled
* Monday 1
* Tuesday 2
* Wednesday 3
* Thursday 4
* Friday 5
* Saturday 6
* Sunday 7
*
*****************************************************

v_DateIn = Iconv(DateIn,"D-YMD[4,2,2]")

v_DayOfWeek = Oconv(v_DateIn,"DW")

If v_DayOfWeek < DayOfWeek
Then
v_DateOut = v_DateIn - v_DayOfWeek - (7 - DayOfWeek)
End Else If v_DayOfWeek > DayOfWeek
Then
v_DateOut = v_DateIn - v_DayOfWeek + DayOfWeek
End Else
v_DateOut = v_DateIn
End

Ans = Oconv(v_DateOut,"D-YMD[4,2,2]")

**************************************************

Could easily be converted to stage variables if you wanted to.