date function

[kruthika]

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!!

[gh_amitava]

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

[rsrikant]

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

[ray.wurlod]

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).

[kruthika]

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,

[ray.wurlod]

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

[vbeeram]

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

[leo_t_nice]

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