Possibility to change the date format of the generated SQL. #105

jflaska commented 2026年02月06日 11:25:15 +01:00

It would be useful if, when generating the SQL, it was possible to copy the date format either as it is displayed according to the format in "Data formatting", or it was possible to specify the date format in the Connect profile.
We have an Informix database that has the date set according to the language and thus respects the national date format in SQL as well. Thus, the generated SQL cannot be used in this case.

It would be useful if, when generating the SQL, it was possible to copy the date format either as it is displayed according to the format in "Data formatting", or it was possible to specify the date format in the Connect profile. We have an Informix database that has the date set according to the language and thus respects the national date format in SQL as well. Thus, the generated SQL cannot be used in this case.

sql-workbench commented 2026年02月06日 16:01:08 +01:00

When you use "Copy data to clipboard", the dialog that shows up then, contains a drop down to select the type of DATE (including TIMESTAMP) literals to be used.

For Informix, I have configured the formats to be used:

So if you are connected to an Informix database choosing "DBMS" should use that format.

If those formats are wrong, you can override them by putting the correct syntax into workbench.settings.

The default used when copying to clipboard, can be configured using Tools -> Options -> SQL Generation.

When you use "Copy data to clipboard", the dialog that shows up then, contains a drop down to select the type of DATE (including TIMESTAMP) literals to be used. For Informix, I have configured the formats to be used: https://codeberg.org/sql-workbench/workbench/src/commit/ccef3a1f5a0a3e28cfbd65bcb44c7f7227fc1ce7/src/main/resources/workbench/resource/default.properties#L1179 So if you are connected to an Informix database choosing "DBMS" should use that format. If those formats are wrong, you can override them by putting the correct syntax into `workbench.settings`. The default used when copying to clipboard, can be configured using Tools -> Options -> SQL Generation.

jflaska commented 2026年02月09日 12:53:37 +01:00

DATETIME is fine because it is not affected by the language setting. I have a problem with the DATE type that I need to use in the form dd.mm.yyyy. Is there any way to change this? None of the options for Copy data to clipboard -> Date literal type will allow this. I don't see it in workbench.settings either.

DATETIME is fine because it is not affected by the language setting. I have a problem with the DATE type that I need to use in the form dd.mm.yyyy. Is there any way to change this? None of the options for Copy data to clipboard -> Date literal type will allow this. I don't see it in workbench.settings either.

sql-workbench commented 2026年02月09日 13:23:28 +01:00

The file workbench.settings only contains custom configurations, not the default settings.

You will need to add the configuration manually.

Please try the following:

workbench.sql.literals.informix_dynamic_server.date.pattern=''dd.MM.yyyy''

This will be used when you use "Copy data as SQL", but unfortunately not if you use "Copy data to clipboard" and then choose the date literal type "dbms" in the dropdown labeled "Date literal type"

A workaround for that is to add Informix as another "dbms" to that dropdown by adding the following line to workbench.settings

workbench.sql.literals.types=jdbc,ansi,dbms,default,oracle,sqlserver,sybase,informix_dynamic_server

The you will see "informix_dynamic_server" as an option and if selected it will use the format specified (the drop down will list the entries in the order you define them in the property, so if you want Informix to be the first one, just put it first in the list)

The file `workbench.settings` only contains custom configurations, not the default settings. You will need to add the configuration manually. Please try the following: workbench.sql.literals.informix_dynamic_server.date.pattern=''dd.MM.yyyy'' This will be used when you use "Copy data as SQL", but unfortunately not if you use "Copy data to clipboard" and then choose the date literal type "dbms" in the dropdown labeled "Date literal type" A workaround for that is to add Informix as another "dbms" to that dropdown by adding the following line to workbench.settings workbench.sql.literals.types=jdbc,ansi,dbms,default,oracle,sqlserver,sybase,informix_dynamic_server The you will see "informix_dynamic_server" as an option and if selected it will use the format specified (the drop down will list the entries in the order you define them in the property, so if you want Informix to be the first one, just put it first in the list)

jflaska commented 2026年02月09日 14:05:14 +01:00

Thanks!

Thanks!

sql-workbench commented 2026年02月10日 08:16:30 +01:00

Let me know if it works (then I'll close the issue)

Let me know if it works (then I'll close the issue)

jflaska commented 2026年02月10日 10:24:59 +01:00

Yes it works, thank you very much for your help.

Yes it works, thank you very much for your help.