Problems with dates
Moderators: chulett, rschirm, roy
The simplest 'trick' to get the last day of a month is to still set the day portion to '01' like you do for the first day. Then, bump the month to the next month and increment the year if you need to as well. Then subtract 1 day.
For example:
In DataStage, once you get the date incremented, use Iconv to get the date into internal format, subtract 1 from the internal date and then use Oconv to turn that back into a date in the format you need.
For example:
Code: Select all
200405 -> 2004-05-01 -> 2004-06-01 -> 2004-05-31
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Transforms do not have the capability to deal with job parameters. (I am sure this is documeted somewhere; it is obvious when you use the Expression Editor and no "job parameter" appears on the list of available operands.)
If you need to use a parameter you will have to use in-line expression within your Transformer stage equivalent to the defining expression for your Transform. That is:
If you need to use a parameter you will have to use in-line expression within your Transformer stage equivalent to the defining expression for your Transform. That is:
Code: Select all
ConvertMonth("#MES#", "F")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
... but you could not do that in a TRANSFORM, because Transforms do not have support for Job parameters.
The next goal, therefore, is to find the most efficient in-line expression possible. I believe thatis more efficient than your construction.
The next goal, therefore, is to find the most efficient in-line expression possible. I believe that
Code: Select all
ConvertMonth(FILENAME, "F")
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
True, because in the WHERE clause you can only use valid SQL functions.
ConvertMonth is a DataStage function, not an SQL function. Transforms, similarly, can not be used in place of SQL expressions in SQL statements.
If your database has a "first of the month" and/or a "date diff" function, by all means use those. Otherwise you're pretty much going to have to use two parameters; one for the first dayof the month and one for the last day of the month.
For example, in Red Brick SQL you could express the last day of August 2000 with the following SQL expression:This adds one month to the date then subtracts one day from that result. You could generate the required date format from your job parameter using sub-string and concatenation techniques.
Your requirement to use this in a WHERE clause has not been mentioned previously in this thread.
I think you will find it easiest, if they are unvarying, to use two parameters, one for the first day of the month and the other for the last day of the month. In this way you don't have to re-calculate the last day of the month for every row processed.
ConvertMonth is a DataStage function, not an SQL function. Transforms, similarly, can not be used in place of SQL expressions in SQL statements.
If your database has a "first of the month" and/or a "date diff" function, by all means use those. Otherwise you're pretty much going to have to use two parameters; one for the first dayof the month and one for the last day of the month.
For example, in Red Brick SQL you could express the last day of August 2000 with the following SQL expression:
Code: Select all
dateadd(dd,-1,dateadd(mm,1,date('2000-08-01')))
Your requirement to use this in a WHERE clause has not been mentioned previously in this thread.
I think you will find it easiest, if they are unvarying, to use two parameters, one for the first day of the month and the other for the last day of the month. In this way you don't have to re-calculate the last day of the month for every row processed.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
You're welcome. My first week was in nineteen mumble mumble!
There is a huge amount to learn in this product. It's really good but you have to learn what you can do (and where) and what you cannot do. Do take the Ascential training if you can. Otherwise, experiment!
Especially in the Designer client but, to some extent, in all clients, use the right mouse button everywhere you can think of on the screen, including in the background areas. You might be amazed at what you find!
Where there's a scroll bar appearing, investigate where it goes.
Check out the View menu to determine what you're not seeing (there's only a limited amount of screen real-estate, after all).
And, once you have come up with your preferred solution, share it here so that other newcomers may benefit.
There are at least three ways of doing anything in DataStage, sometimes more. It's only with experience that you learn which is appropriate - or more efficient - in particular circumstances.
There is a huge amount to learn in this product. It's really good but you have to learn what you can do (and where) and what you cannot do. Do take the Ascential training if you can. Otherwise, experiment!
Especially in the Designer client but, to some extent, in all clients, use the right mouse button everywhere you can think of on the screen, including in the background areas. You might be amazed at what you find!
Where there's a scroll bar appearing, investigate where it goes.
Check out the View menu to determine what you're not seeing (there's only a limited amount of screen real-estate, after all).
And, once you have come up with your preferred solution, share it here so that other newcomers may benefit.
There are at least three ways of doing anything in DataStage, sometimes more. It's only with experience that you learn which is appropriate - or more efficient - in particular circumstances.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
I suspect you are confusing a Transform (which is a discrete DataStage object, an encapsulated, pre-stored expression, stored in the Transforms branch of the Repository) with a Transformer stage (which is not a discrete DataStage object but, instead, must exist within a DataStage job).
I have just checked on version 5.2 and 7.0 DataStage systems, and the operand menu does not show Job Parameter in Transforms in either.
Footnote: What you see in the Stage Types branch of the Repository is the Transformer stage type; if you prefer, the class from which all Transformer stage objects is instantiated.
I have just checked on version 5.2 and 7.0 DataStage systems, and the operand menu does not show Job Parameter in Transforms in either.
Footnote: What you see in the Stage Types branch of the Repository is the Transformer stage type; if you prefer, the class from which all Transformer stage objects is instantiated.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.