Last Date of Previous Month

[Prashantoncyber]

Hi all,

We need to run a job once any time in a month.
Which can populate a column in the table/file with the last date of previous month.
Your help shall be appreciated.

Thanks
Prashant

[ArndW]

I am not at a PX computer or have access to the documentation, but the route I would take is:

Code: Select all

DateFromDaysSince(-1,StringToDate(DateToString(CurrentDate(),'%yyyy%mm%dd')[1,6]:'01'))

You might have to weed out the syntax errors and/or function names yourself, but the basic functionality is

a) take today's date as a string with the 'dd' part at the end
b) rebuild a date variable by replacing the 'dd' with the first day of the month
c) subtract one day from that date

[Prashantoncyber]

Tried by but not fetching proper result.
Looks like need some other approach.

Thanks
Prashant

[Maveric]

ArndW wrote: DateFromDaysSince(-1,StringToDate(DateToString(CurrentDate(),'%yyyy%mm%dd'& ...

I guess this will work if u give the first day of the month. -1 will probably give the previous date. try giving the day instead of one and see. i.e if the date is 21-11-2006. give the argument as -21 instead of -1. See if this works.

[Ragunathan Gunasekaran]

Hi ,
Are you using oracle table as the target stage ?.....
If so you can edit the insert statement generated by datastage to the following
insert into table_name values (...,trunc(sysdate) -1)
i think this would solve the problem . But if the datastage server and the oracle server are not in the same machine this may not produce an expected date. . .

Just check this out . . . It may not be a solution within the datastage job design but from the database side

[JeroenDmt]

Tried by but not fetching proper result.
Looks like need some other approach.

Thanks
Prashant

The approach is correct.
Try

Code: Select all

DateFromDaysSince(-1,StringToDate(DateToString(CurrentDate(),'%yyyy-%mm-%dd')[1,8]:'01'))

[Prashantoncyber]

Tried by but not fetching proper result.
Looks like need some other approach.

Thanks
Prashant

[kumar_s]

Hi Prashant,
What is the exact procedure that you tried and what is the result that you got which is not expected.
Have you made a search?
Have you tired

Code: Select all

DateFromDaysSince(-1, StringToDate(DateToString(CurrentDate(),"%yyyy%mm"), "%yyyy%mm") : "%yyyy-%mm-%dd") 

[ArndW]

Odd, this approach works for me. Please post what the incorrect result is.

[Prashantoncyber]

After carefully checking, it worked properly.

Thanks guys for your help.

Time to mark this topic resolved.

Thanks

[fareeda_b]

Hi Prashanth ,
could you please explan me how this date issue got resolved ?

thanks
fareeda

[fareeda_b]

Hi Prashanth ,
could you please explan me how this date issue got resolved ?

thanks
fareeda