Export data from a DB2 to import into an Oracle DB (unfortunately, I cannot access it from DbVis anymore) to create test data
When I use "export selection" on a table with SQL option, I only get the possibility to export literals for the date/time typed fields (yellow marked areas of the picture attached). IMHO, this is a bit short because it either means to exactly know what format the target database is expecting or it requires me to adapt the generated script. I have not been able to create a format string to avoid adaptions. I only have workaround settings (attached) that makes adaption of the generated script easier.
Provide an item in the drop down list that generates to_timestamp etc. commands with appropriate formats, e.g. the one of the DbVis settings.
I will register this as an improvement request and bring it up for discussion.
Thanks for suggesting this!
Hej igen Thiemo,
I probably misunderstood what you were asking for. What do you refer to with "the one of the DbVis settings"? Can you please clarify (ideally by an example) what you would like to see added in the dropdown?
To clarify the settings term I added the following picture.
I just noticed that the picture attached to the original post might only be visible as a thumbnail where nothing can be recognised, so I put it again.
I would appreciate a drop-down menu item (i.e. within the yellow block) that creates the literal for the to_date, to_time and to_timestamp function directly, e.g. to_timestamp('#the_actual_timestamp_value_as_string_in_the_following_format#', 'YYYY-MM-DD HH24:MI:SS.FF9'), where the default format is the one of DbVis settings - see above.
I hope, I could make myself understood.
You're doing a good job explaining, but there are many ways to approach this and even more ways to misunderstand ;-)
So, rewriting in my own words; the core problem is that you don't want the exported data to hold a formatted date/time value, but a function call to translate to the actual format at a later time. That is, instead of getting the output '2020-12-17' you want TO_DATE('2020-12-17', 'yyyy-mm-dd') (or some other function call).
Is this correct?
If so, would it be possible to get the desired result by appending the surrounding text on the next screen ("columns") in the export wizard?
Given the following data and export settings:
You would get the following results:
Question: Would this give you the result you want?
If so, would this be a good enough solution, or would you like to see this simplified by doing this directly when setting the date/time format?
The example you gave is just right at the point. I was not aware of the possibility to fiddle the export in the export grid dialogue. And yes again, I would like to see the simplification as you detailed out. My concerns with that are twofold:
Ok, good that we now have a clear understanding :-)
I understand and applaud your laziness; without people like you, we'd be out of business :-D
I will bring this info into the issue I registered and we'll look into it.
Thanks again for your suggestion, very valuable (as usual).
You are welcome and it is all my pleasure. I appreciate your open ear.