Page 1 of 1
Last Date of Previous Month
Posted: Mon Jul 16, 2007 2:20 am
by 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
Posted: Mon Jul 16, 2007 2:35 am
by 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
Posted: Tue Jul 17, 2007 3:24 am
by Prashantoncyber
Tried by but not fetching proper result.
Looks like need some other approach.
Thanks
Prashant
Posted: Tue Jul 17, 2007 3:46 am
by 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.
Re: Last Date of Previous Month
Posted: Tue Jul 17, 2007 4:06 am
by 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
Posted: Tue Jul 17, 2007 5:09 am
by JeroenDmt
Prashantoncyber wrote: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'))
Posted: Tue Jul 17, 2007 5:09 am
by Prashantoncyber
Tried by but not fetching proper result.
Looks like need some other approach.
Thanks
Prashant
Posted: Tue Jul 17, 2007 5:11 am
by 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")
Posted: Tue Jul 17, 2007 9:41 pm
by ArndW
Odd, this approach works for me. Please post what the incorrect result is.
Posted: Mon Jul 23, 2007 7:27 am
by Prashantoncyber
After carefully checking, it worked properly.
Thanks guys for your help.
Time to mark this topic resolved.
Thanks
Posted: Mon Dec 08, 2008 3:57 am
by fareeda_b
Hi Prashanth ,
could you please explan me how this date issue got resolved ?
thanks
fareeda
Posted: Mon Dec 08, 2008 3:58 am
by fareeda_b
Hi Prashanth ,
could you please explan me how this date issue got resolved ?
thanks
fareeda