DSXchange

After modifying the date string in job properties "Defaults" tab at job level to %mm/%dd/%yyyy and then using currentdate() in transformer stage in data stage parallel job
Resultunt date value obtained in %yyyy-%mm-%dd format

But i need the resultant in %mm/%dd/%yyyy format

What i need to do to get the above result

Dates and timestamps are stored in binary form during processing and have no format. It is only when importing or exporting that a conversion is made. Thus the question is at which point in processing are you converting from date to string? It is at that point that the system uses a conversion. Is this happening in a stage in the job? I am not sure why the job defaults you set haven't been used, but an option would be to explictly set formatting for that one field, i.e if you writing to a sequential file then go to the column list in the output stage, right-mouse-click to get the "edit row" and then explicitly enter a Date Type -> Format String of "%mm%dd/%yyyy".

My requirement is to load data with the same job using ODBC connector to oracle and Mysql databases,but i am not able to load due to date format issues.

So for that i need to use the same job to load data with the format supported by oracle i.e., %dd-%mmm-%yy. Please Help on this.

And can u tell me for what default date formats w.r.t project level and job levels are used in Datastage ?
If it is not use to convert in the job, then what is the need of it!

ujala,

I've marked my previous post as non-premium so that you can read it completely.

No, you didn't... but I just did.

And off we go to the proper forum.

I need the conversion at first stage i.e., odbc connector - I am using a SQL query where i am fetching data between two dates(last_run_date and current_date)
where the current date is passed from the sequence job(i.e., DSJOBSTARTDATE) which is in the format %yyyy-%mm-%dd, but to get the data from the oracle data base i need the data in %dd-%mmm-%yyyy format

So you need to change the date format from a string (your parameter) to another string (the SQL query) and that is why the DataStage job setting aren't affecting you.

In the SQL you should do a "TO_DATE(#DSJOBSTARTDATE#,"yyyy-mm-dd")"

Can you please mark it as non premium content!

It doesn't really work that way. And you do not need the DATE in any particular format, learn to use the Oracle TO_DATE() function.

Well thanks for your reply,

But i have to use the same job for mysql and oracle and i have found a workaround, if i add a key word Timestamp before the date it works for most of the databases, but doesnot support MSSQL

Any option to work with it globally for all sets of jobs.