Export table and date formatting?

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.

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

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.

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