Last Date of Previous Month
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am
Last Date of Previous Month
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
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
I am not at a PX computer or have access to the documentation, but the route I would take is:
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
Code: Select all
DateFromDaysSince(-1,StringToDate(DateToString(CurrentDate(),'%yyyy%mm%dd')[1,6]:'01'))
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
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am
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.
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.
-
- Participant
- Posts: 247
- Joined: Mon Jan 22, 2007 11:33 pm
Re: Last Date of Previous Month
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
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
Regards
Ragu
Ragu
The approach is correct.Prashantoncyber wrote:Tried by but not fetching proper result.
Looks like need some other approach.
Thanks
Prashant
Try
Code: Select all
DateFromDaysSince(-1,StringToDate(DateToString(CurrentDate(),'%yyyy-%mm-%dd')[1,8]:'01'))
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am
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
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")
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 108
- Joined: Wed Jul 28, 2004 7:15 am