get lastmonth timestamp in transformer

[smile.achieve]

DATEADD(month, -1, getdate()) This is the function in SQL server which gives timestamp corresponding to last month. Now I want to implement the same logic in one of the columns for transformer. I have tried serveral predefined options of datetime and current timestamp functions in transformer which are not giving last month date.. Can some one help me getting this logic. The date should correspond to currentdate minus one month.


Thanks in advance


Ranjan Mahapatra

[chulett]

Your example looks wrong for the description posted... -11 months?

It would probably be helpful to know what you've tried already, for example did you give DateFromDaysSince() a try? Granted you'd have to use 30 or 31 for the number of days in a month, but it would get you... close.

I'd also be curious what the rules are for dates that don't exist in the previous month - for example, what would March 31st minus one month equal?

[ray.wurlod]

Your edit has removed the example, leaving us even more in the dark.

At a guess, I'd say what you need to do is subtract the number of days in the previous month from the current date.

[smile.achieve]

No. Ray i dont see any issues with the example now. If you are aware of datedadd() function of sqlserver may be you will be able to get a clear picture. However i tried solving this by getting the month number first and subtracting 30/31 days based on the month number(got from MonthFromdate()).How ever my code does not give valid results for following dates. 30th March,31st March.To handle this is seperate if else clause i need to get some function like DayfromDate() which is not available.

[chulett]

Check the Parallel Job Developer's Guide for the list of available functions. Perhaps ...

MonthDayFromDate()
WeekdayFromDate()
DaysInMonth()

Depending on how you are looking to solve this.

[BI-RMA]

If on 8.5 or later: You can use DateOffsetByComponents(base_date,0,1,0).

This allows You to add 1 month to your base date.