CurrentTimestamp() before 3 months

prabudsx · Post by **prabudsx** » Thu Jun 30, 2011 7:09 am

Objective:
Timestamp value which is 3 months before of CurrentTimestamp().

Please assist...!

jwiles · Post by **jwiles** » Thu Jun 30, 2011 10:57 am

To retain a timestamp value, two possible options come to mind:

1) Use the TimestampFromSecondsSince(). You'll need to calculate the number of seconds that equate to three months (there are 86400 seconds in a day)

2) Get date and time separately--CurrentDate() and CurrentTime(). Then use DateFromDaysSince() to calculate the date 3 months previous to CurrentDate() and create a timestamp from the resulting date and CurrentTime() value.

Regards,

pandeesh · Post by **pandeesh** » Thu Jun 30, 2011 12:18 pm

3months before: it means depending on the current month we need to calculate whether the previous 3 months have 30 or 31st days in those.
correct?

jwiles · Post by **jwiles** » Thu Jun 30, 2011 3:19 pm

Or 29 or 28 days (for February).

Ultimately the requirement depends upon the actual business rules (i.e. the client's requirements) for the calculation. They may simply state 90 days previous. Many accounts receivables calculations are based upon 30, 45, 60 and 90 days and not necessarily number of days in the month (although adjustment may be made from time to time).

Regards,

ray.wurlod · Post by **ray.wurlod** » Thu Jun 30, 2011 4:47 pm

Sometimes the business rule then specifies that the day in question must be a business day (not a weekend or a holiday).

What are your business rules?

prabudsx · Post by **prabudsx** » Thu Jun 30, 2011 11:33 pm

Ray,
My Business rule is to calculate the Timestamp value which is 3 months before current timestamp and NOT on the total number of days (90).

I tried Julian date option which produce result based on total number of days(90) not on the months.

Please advice how to achieve ...!

ray.wurlod · Post by **ray.wurlod** » Fri Jul 01, 2011 12:08 am

OK, what is the timestamp three months before 2011-05-31 11:12:13 ?

prabudsx · Post by **prabudsx** » Fri Jul 01, 2011 2:01 am

Ray,
The expected output needs to be "2011-02-28 11:12:13.000000"...!
Please advise

ray.wurlod · Post by **ray.wurlod** » Fri Jul 01, 2011 6:25 pm

Create a routine that subtracts 3 from the month if it is 4 through 12, or adds 9 to the month and subtracts 1 from the year if the month is 1 through 3, then adjusts the date backwards until it is valid. From that new date, rebuild the timestamp.

Alternately you could perform these steps in stage variables.