Problems with dates

denzilsyb · Post by **denzilsyb** » Wed Jun 16, 2004 6:15 am

I just checked if there are routines to help with this and it looks like you're going to have to use the MONTH.TAG with the Routine ConvertMonth (find it in Manager -> Routines -> Examples )

let us know if you got it!

dnzl

chulett · Post by **chulett** » Wed Jun 16, 2004 7:04 am

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:

Code: Select all

200405 -> 2004-05-01 -> 2004-06-01 -> 2004-05-31

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.

ray.wurlod · Post by **ray.wurlod** » Thu Jun 17, 2004 4:10 pm

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:

Code: Select all

ConvertMonth("#MES#", "F")

suma · Post by **suma** » Fri Jun 18, 2004 1:33 am

Hi,,,

Try this ,,It works Fine,,,
Here FILENAME is the Job Parameter,,,,

ALl the functions are self Explanatory,,, G
Left(DateYearFirstToODBCWithTime(Oconv(MONTH.FIRST(FILENAME),"D-YMD")),19)

Get back to me for any help :D

Regards,,

Mary Suma

ray.wurlod · Post by **ray.wurlod** » Fri Jun 18, 2004 1:47 am

... 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 that

Code: Select all

ConvertMonth(FILENAME, "F")

is more efficient than your construction.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 18, 2004 2:43 am

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:

Code: Select all

dateadd(dd,-1,dateadd(mm,1,date('2000-08-01')))

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.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 18, 2004 3:17 am

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.

suma · Post by **suma** » Fri Jun 18, 2004 4:57 am

Ray,

Job Parameter works very well inside a Transform, We can Right click and get the Job Parameter.And this code works very well for me!!

if i am Wrong,Where Exactly!!!!

ray.wurlod · Post by **ray.wurlod** » Fri Jun 18, 2004 5:02 am

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.

suma · Post by **suma** » Fri Jun 18, 2004 7:30 am

ThanX A lot RAY!!! I have never used those Functions to solve Date Problems,,,It seems to be more easier.I solve most of the Date Problems with Transforms inside the Transformer stage.
The one i explained was the same,,,

Regards,
Suma