Unable to get the date in the required format

ujala · Post by **ujala** » Wed Jul 11, 2012 1:16 am

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

ArndW · Post by **ArndW** » Wed Jul 11, 2012 1:33 am

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

ujala · Post by **ujala** » Wed Jul 11, 2012 5:23 am

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!

ArndW · Post by **ArndW** » Wed Jul 11, 2012 5:40 am

ujala,

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

chulett · Post by **chulett** » Wed Jul 11, 2012 6:50 am

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

And off we go to the proper forum.

ujala · Post by **ujala** » Wed Jul 11, 2012 7:10 am

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

ArndW · Post by **ArndW** » Wed Jul 11, 2012 7:13 am

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")"

ujala · Post by **ujala** » Wed Jul 11, 2012 7:15 am

Can you please mark it as non premium content!

chulett · Post by **chulett** » Wed Jul 11, 2012 7:26 am

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.

ujala · Post by **ujala** » Wed Jul 11, 2012 8:47 am

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.