DSXchange

Hi,
I want to find end date of previous month for the given date.
suppose,if the given date is 15/jul/2004 then expected output should be 30/jun/2004.

Is there any way to achieve this using built in functions?
suggest me if there is any other way to do this.

Thanks!!

Hi,

You can do by this process..

1. Find the current month number by the function 'MonthFromDate'.. If you give MonthFromDate(2004-07-15) , it will return 07, i,e July..

2. write your own logic (12 if-then else loop) to find the last day of the previous month..

Regards
Amitava Ghosh

We don't need 12 if then loops....

since the end date of a month can only be 28, 29, 30, 31 and we know the end date for all the months, two loops for 30 & 31 and one nested loop for feb is enough.

But I feel there should be a better way of doing then if then else loops.

Anyone has some idea on this?

Regards,
Srikanth

The end day of the previous month is the day before the first day of the current month. The latter is easy to construct. I will have to check what DateDiff functions are available in PX to do the "day before" part.

If the data are coming out of a database, it's almost certain to have date manipulation functions that allow you to do this.

You can use a BASIC Transformer, which gives you access to Transforms such as MONTH.FIRST and MONTH.LAST, which make life very easy indeed (at the cost of a small performance hit).

I actually tried MONTH.LAST in the transformer actually, since its a server job function and i am working on the parallel job, its not available in it. So i am trying to put the stage variable for the same. I am not sure of its performance issue, could any one please tell how to do this in a better way.

Thanks for ur response,

That's why I suggested a "BASIC Transformer" stage, not a "Transformer" stage.

Hi Kruthika,
Follow these steps to get previous month last date:

1.Get month no using MonthfromDate() then store in a stage variable(say..sgMON) OR (you can use LEFT or RIGHT or Field to get month no)

2.Get year using YearfromDate() function then store in a stage variable(say... sgYN)

3.Concatinate the two stage variables with "01" date and use StringTodate() function then store in a stage variable(say ..sgDate)
StringtoDate(sgMON:"01":sgYN,"%mm/%dd/%yyyy")=sgDate

4.Find the Julian Day then store in a stage variable(say...sgJday)
JulianDayFromDate(sgDate)=sgJday
Here u get julian days for the current month first day,so if you subtract one day from that u will get last month end date Julian day.
say---
sgday-1=sgJday2(store again in a stage variable)

5.now if you convert this o/p(Julian day of last month end date),u will get the required o/p.Use datefromJulianday() function
say...
DatefromJulianday(sgJday2)


thanks
Thirupath Reddy

If you are using the transformer, this method works too;

create a stage variable of type varchar/char and length 10, and give the derivation as something like

TimestampFromTimet ( TimetFromTimestamp ( LNK_IN.col1 [1, 8] : "01 00:00:00" ) -1 )

This example assumes the date is in yyyy-mm-dd format and takes the first 8 characters (year, month and separator, yyyy-mm- ), then appends '01 00:00:00' to give midnight on the first day of the month. This is converted to a timet and subtracts 1 (second?) to give a time on the previous day, and is then converted back to a timestamp. The stage variable is only 10 characters long so only the date is retained.

HTH