How can we help you today?
Data export as SQL: only implicit date/time formats available
Peer Törngren
Hej Thiemo
I will register this as an improvement request and bring it up for discussion.
Thanks for suggesting this!
Hälsningar
Peer
Peer Törngren
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?
Hälsningar
Peer
Thiemo Kellner
Hej Peer
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.
Hälsningar
Thiemo
Peer Törngren
Hej Thiemo
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?
Example:
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?
- This approach would let you/force you to add this fore each column.
- Doing it in the previous screen as a general date/time format would let you/force you to use the same format for all date/time columns.
Hälsningar
Peer
Thiemo Kellner
Hej Peer
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:
- I am lazy (that's the reason I have become a programmer ;-) ) and entering is tedious. I am not sure, but I believe what ever you enter in the export grid dialogue does not get saved when saving settings (would wonder how to you would get rid of the acutal table specifics).
- I am lazy and entering manually is error prone in my praxis. That's why I sometimes over-automate. :-)
Peer Törngren
Hej Thiemo
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).
Hälsningar
Peer
Thiemo Kellner
You are welcome and it is all my pleasure. I appreciate your open ear.
Thiemo Kellner
Hej
Use case
Export data from a DB2 to import into an Oracle DB (unfortunately, I cannot access it from DbVis anymore) to create test data
Problem
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.
Solution proposal
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.
Varma hälsningar
Thiemo