Start a new topic

Export table and date formatting?

[This topic is migrated from our old forums. The original author name has been removed]

Hi, Using dbVisualiser 7.1.3 64 bit on windows 7. We're running against an Informix 11.5 database. Dates in database are of the format 'dd/mm/yyyy'. In our database 'connection properties' -> 'driver properties' we always set DBDATE = dmy4/ to match above format. I want to export a table to the sql editor so I can modify a couple of values and run the sql to get the new rows into the table. However, it all goes bad on the exported sql with date fields. Anywhere there is a date column it adds in the 'TO_DATE' function but in a way that doesn't work. for example if the date column holds '31/01/1991': if I don't change the 'export data' -> 'data format settings' for date fields (i.e. leave it at yyyy-MM-dd) I get: TO_DATE('1991/01/31', '%Y-%m-%d') but running sql gives: Error Code: -1277, SQL State: IX000] Input does not match format specification. if I change the 'export data' -> 'data format settings' for date fields to dd/mm/yyyy I get: TO_DATE('31/01/1991', '%Y-%m-%d') but running sql gives same error as before. What does work is just having the date put out like '31/01/1991'. Is there a way to export a table that has dates without the to_date function being inserted in the mix? Or to have the to_date function be correctly formatted to match the data format settings selected. e.g. to_date('31/01/1991', '%d/%m/%Y') Thanks, Bryce Stenberg.

[This reply is migrated from our old forums.]

Re: Export table and date formatting?
Hi Bryce, I'm unable to reproduce what you describe. Regardless of how the date is presented in the Data tab, I always get an INSERT statement with a TO_DATE() with the date formatted as yyyy-MM-dd and a matching pattern, e.g. TO_DATE('1991-01-31', '%Y-%m-%d') I've tested by setting a DBDATE driver property to "dmy4/", but it has no effect as far as I can tell. I have also changed the Date Format in Tool Properties to "dd/MM/yyyy", but I still can't reproduce it. Have you done any other customization of the DbVisualizer defaults, such as edited any config files by hand? Best Regards, Hans
[This reply is migrated from our old forums. The original author name has been removed]

Re: Export table and date formatting?
Hi Hans, There have been no customisations to any dbvisualiser configuration files. But I think I may have been doing it wrong yesterday, sorry. Possibly first time I changed 'data format settings' and never got it right again after that. Today, having ready your reply and after a reboot, I tried it again and it just works, as long as I don't change any data format settings. If I do change the data format for date to read as a normal date (normal for us that is 'dd/mm/yyyy') in the script, the to_date functions mask is not modified to match. Maybe it should? Regards, Bryce.
[This reply is migrated from our old forums.]

Re: Export table and date formatting?
Hi Bryce, Ah, yes, changing the format used for export cause the problem you describe. I agree that it would be nice if the pattern/mask was changed to match the selected presentation format. I have created a feature request ticket for this. Best Regards, Hans