Page 1 of 1
getting data for the last three months
Posted: Tue Jun 18, 2013 3:30 am
by uzl
I have the following requirement. I need to extract data for the last three months. So today (18-06-2013) I have to extract data from 01-03-2013 to 31-05-2013.
I managed to get the enddate : OConv((MONTH.FIRST(MONTH.TAG(DATE()))) ,'D-YMD[4,2,2]') :' ':Oconv(Time(),'MTS.') gives me "2013-06-01 11:13:17.000". My first question is how to transform this to "2013-06-01 00:00:00.000".
My second question is : how do I go three months back ?
Posted: Tue Jun 18, 2013 4:54 am
by ArndW
OConv((MONTH.FIRST(MONTH.TAG(DATE()))) ,'D-YMD[4,2,2]') :' 00:00:00'
Posted: Tue Jun 18, 2013 5:18 am
by uzl
This solves my first question. I'm a bit embarressed it was so simple
. Now my second question?
Posted: Tue Jun 18, 2013 6:14 am
by ArndW
How do you define '3 months back'?
does it mean subtract 90 days? or the first day of the month when you subtract 3 from the current month? Or some other rule?
Posted: Tue Jun 18, 2013 6:36 am
by uzl
substract 3 from the current month.
Posted: Tue Jun 18, 2013 6:51 am
by ArndW
One last question - you are using OCONV() so I would assume a server job, but the question is posted in the parallel forum. The answer depends upon which you are really using.
Posted: Tue Jun 18, 2013 7:26 am
by uzl
It's a server job. We were told only to use serverjobs. We started working with datastage with version 8. I have to admit that I don't understand the distinction between the two fora.
Posted: Tue Jun 18, 2013 7:42 am
by chulett
You posted in the EE forum which is dedicated to PX / Parallel jobs and you marked your Job Type as Parallel as well, hence Arnd's question. I'm going to fix both issues. Hang on...
Here we are, correct forum and job type.
Posted: Tue Jun 18, 2013 4:07 pm
by ray.wurlod
I suspect the question relates to a sequence governing parallel jobs.
You might like also to investigate using DataStage routines such as the AddMonths routine available
here.
getting data for the last three months
Posted: Wed Jun 19, 2013 12:14 am
by bhasds
Hi uzl,
You may use the following to get the 3 month back date-
Code: Select all
'01':"-":(if DSJobStartTimestamp[6,2] - 3 <10 Then 0:DSJobStartTimestamp[6,2] - 3 Else DSJobStartTimestamp[6,2] - 3):"-":DSJobStartTimestamp[1,4]:'00:00:00'
Re: getting data for the last three months
Posted: Wed Jun 19, 2013 7:23 am
by uzl
Thanks bhasds. We use different date-formats but your example has set me in the right direction.
Code: Select all
prepBeginMaand_uv.UV_beginMaand : if DSJobStartTimestamp[6,2]-3 <= 0 then (DSJobStartTimestamp[6,2] + 9) else ('0' : DSJobStartTimestamp[6,2]-3)
prepBeginMaand_uv.UV_beginJaar : if DSJobStartTimestamp[6,2]-3 <= 0 then (DSJobStartTimestamp[1,4] - 1) else DSJobStartTimestamp[1,4]
dateBegin_UV : prepBeginMaand_uv.UV_beginJaar : '-' : prepBeginMaand_uv.UV_beginMaand : '-01 00:00:00.000'
=> the derivation of the year works only for years > 2000
Posted: Wed Jun 19, 2013 5:07 pm
by ray.wurlod
The AddMonths routine works for any date.
Posted: Fri Jun 21, 2013 7:20 am
by uzl
We don't have experience with added routines. How does this work with upgrades ?
Posted: Fri Jun 21, 2013 3:37 pm
by ray.wurlod
Perfectly. These routines have been in use since version 3.