BIuser wrote:Hi all
I want my job to have the previous month-end(September) date in this month(october).
what function or code can i use
Try using a transfor to do it. We are currently using it to find the month-begin using a transform which takes 2 arguments, first is date and second is number of days to be substracted. For ex if i supply the parameter as 17-Oct-2007 and 30 days(1 month) then it will give me date as 1-sep-2007. In your case you can get the last day. Copy the below code in a txt file and import it using datastage manager. You will get a transform as DIFFMONTHFIRST. You can use it in the transformer stage of your job.
BEGIN HEADER
CharacterSet "CP1252"
ExportingTool "Ascential DataStage Export"
ToolVersion "4"
ServerName "10.1.1.211"
ToolInstanceID "SWIMJOBS"
MDISVersion "1.0"
Date "2004-10-18"
Time "12.11.05"
ServerVersion "7.5"
END HEADER
BEGIN DSTRANSFORMS
BEGIN DSRECORD
Identifier "DIFFMONTHFIRST"
DateModified "2004-09-19"
TimeModified "10.50.52"
OLEType "CTransform"
Readonly "0"
Category ""
ShortDesc "Substracts 6 Months From a Date"
Description =+=+=+=
This transform takes two parameters. first is a date in internal format and second is a no. of days to be substracted. It substracts the days from the date and gives the last day of that month as a result.
For Eg.
DIFFMONTHFIRST(13386,180)
gives 01-Feb-2007 which is last day of the date(28-Feb-2007) derived from 13886 (i.e. 24-Aug-2007) - 180 days
=+=+=+=
InputType ""
OutputType ""
NeedsDeffun ""
FunctionName "ConvertMonth"
ArgCount ""
CatalogName ""
ArgList ""
SourcePath ""
Version "2"
Expression "ConvertMonth((Oconv((%Arg1% - %Arg2%), 'D-YM')), "L")"
Arguments "CNameType"
BEGIN DSSUBRECORD
Name "Arg1"
Type ""
END DSSUBRECORD
BEGIN DSSUBRECORD
Name "Arg2"
Type ""
END DSSUBRECORD
END DSRECORD
END DSTRANSFORMS
