how can i have previous month-end date(sept) in this month

[BIuser]

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

[sachin1]

search for forum you will get answer

[kcbland]

Take today's date, substitute the day portion for the first day of the month, internalize the date, subtract one day, externalize the date:

Code: Select all

LastDayOfPreviousMonth = OCONV(ICONV((FIELD(OCONV(@DATE, "D-YMD[4,2,2]"), "-", 1,2):"-01", "D-YMD[4,2,2]") - 1, "D-YMD[4,2,2]")

[JoshGeorge]

Are you looking for a specific kind of code? Because this requirement is posted for the 3rd time by same poster.

Answerd here: viewtopic.php?t=113535 and here : viewtopic.php?p=254377

[ray.wurlod]

Obviously didn't like the answer and intends to keep posting the question until the preferred answer is posted.

[baglasumit21]

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