Page 1 of 2
Get end of Month based on year and Month
Posted: Fri Feb 26, 2010 6:31 am
by puliram
Can any one suggest how to get the last day of a month based on my source data
For Example
I am getting from source 200812(year and Month)
Then my output should be 20081231(end of that month and year)
Say one more example
If I am getting 200802(Year and month)
Then my output should be 20080229(end of month which is leap year)
Any Suggestions please
Posted: Fri Feb 26, 2010 6:41 am
by abhijain
Posted: Fri Feb 26, 2010 6:58 am
by ArndW
The logic is to take your year/month, add one month, create a date of the first day of tht month and then subtract a day.
Posted: Fri Feb 26, 2010 9:01 am
by Sainath.Srinivasan
and to find next month, take first day of current month and add 31 to it.
Posted: Fri Feb 26, 2010 9:27 am
by DSguru2B
Or Just use a Time Dimension table.
Re: Get end of Month based on year and Month
Posted: Tue Mar 02, 2010 12:39 am
by puliram
Can any one give me the solution for below question using datastage Transformer or any solution using DataStage.
Can any one suggest how to get the last day of a month based on my source data
For Example
I am getting from source 200812(year and Month)
Then my output should be 20081231(end of that month and year)
Say one more example
If I am getting 200802(Year and month)
Then my output should be 20080229(end of month which is leap year)
Any Suggestions please[/quote]
Posted: Tue Mar 02, 2010 3:23 am
by ArndW
The solution given in the thread solves your problem as well. Add 1 to the month then subtract a day. That also solves leap year issues.
Posted: Tue Mar 02, 2010 5:08 am
by priyadarshikunal
do you want someone to write the complete derivation?
you can search for it, or read datastage manual for datefromdayssince() function. you may need to use it twice.
Posted: Tue Mar 02, 2010 6:01 am
by rohithmuthyala
Extract the month part alone.
Check if month is 04,06,09,11 then append 30 to the existing string.
Else if month is 02 and (Year%4)=0 then append 29
Else append 31 to the existing string.
The above can be handled using Stage variables in the Transformer stage.
Posted: Tue Mar 02, 2010 3:11 pm
by ray.wurlod
... except where the year number is divisible by 400.
Further, the recent earthquake in Chile may have changed the earth's rotation slightly - the year may be marginally shorter. Do you need to worry about that?
Posted: Wed Mar 03, 2010 12:33 am
by ReachKumar
Adding one more point :
Logic to findout Leapyear:
If (Year%400 = 0 ) or ( Year%100 <> 0 and Year%4 = 0 )
Posted: Wed Mar 03, 2010 8:04 am
by puliram
ReachKumar wrote:Adding one more point :
Logic to findout Leapyear:
If (Year%400 = 0 ) or ( Year%100 <> 0 and Year%4 = 0 )
% dont work in transformer..wat would be the function instead
Posted: Wed Mar 03, 2010 8:04 am
by chulett
Mod()
Posted: Wed Mar 03, 2010 8:09 am
by puliram
ReachKumar wrote:Adding one more point :
Logic to findout Leapyear:
If (Year%400 = 0 ) or ( Year%100 <> 0 and Year%4 = 0 )
% dont work in transformer..wat would be the function instead and also we are missing fed 28 month ending
Posted: Wed Mar 03, 2010 8:11 am
by puliram
rohithmuthyala wrote:Extract the month part alone.
Check if month is 04,06,09,11 then append 30 to the existing string.
Else if month is 02 and (Year%4)=0 then append 29
Else append 31 to the existing string.
we are missing Feb 28 Month ending in this logic and how can we handle % datastage transformer
The above can be handled using Stage variables in the Transformer stage.