How to get previous Sunday date of a particular date in SAP DATA SERVICES

Hello Everyone,

In this blog,I am going to explain how to get the previous Sunday date for a particular date in SAP DATA SERVICES.

For example if we give a particular date as “24-07-2017”, we need to get the output as “23-07-2017”.

Step:1

Create a new  text document (.txt file) with required number of columns.In this scenario, I created a single column as shown below.

Step:2

Create a Project,Job,Workflow,Dataflow in a sequential flow .

Create a new flat file in designer by taking the above text document as input file.

Change the data type of the input field to “DATE” datatype.

Step:3

Drag the created flat file into the data flow,which is considered as input as shown below.

Step:4

The design of the job is shown below.

Step:5

Open the query “QRY_DAY”.Create a new column to calculate the day number of the given date using the function “Day_in_week()” as shown below.

Note:

In Data Services Designer, the day number are assigned by default as follows:

Monday-1

Tuesday-2

Wednesday-3

Thursday-4

Friday-5

Saturday-6

Sunday-7.

Step:6

Open the query “QRY_SUNDAY”.Create a new column to calculate the previous Sunday date by using “Decode()” function as shown below.

Step:7

Save,Validate and execute the job.

Then view the output.

Thanks,

Pujitha