Automatic Timestamp convesrion

[jjeffman]

Hello,

Again about the same topic but this time the question involves regional settings too.

I am coding SQL commands to SQL Server always avoiding to rely into the machine regional settings because it depends on the language the user has chosen to install the OS.

So when I use CONVERT I am always using the same format (121) which corresponds to yyyy-mm-dd hh:nn:ss.zzz like CONVERT( DATETIME,'2018-11-28 18:15:23.654', 121).

Is there a way of setting up the way UniDAC resolves the timestamp macro specifying a certain format to it? UniDAC is converting my strings to CONVERT( DATETIME,'2018-11-28 18:15:23.654'), which raises an exception.

I wouldn't like to check regional settings to create the timestamp string.

Thank you very much.

[jjeffman]

I have found that CONVERT(DATETIME, '2018-29-11 13:22:33.444') returns a valid SQL datetime value on SQL Server 2017. I do not know if this format is reliable to use on the CONVERT function without any style value.

I have made a question to a Microsoft forum ( https://social.msdn.microsoft.com/Forum ... ab907d73c6) which I am waiting for an answer.

I am using this format in the application although I am not sure it will not cause any problem on other windows regional settings.

Best regards.

[jjeffman]

Hello,

It is also important to keep in mind that datetime values in SQL Server have a precision of about 3 milliseconds, so CONVERT(DATETIME, '2018-29-11 13:22:33.444') will be converted to 2018-11-29 13:22:33.443 ( Ymd ).

Best regards.

Jayme Jeffman

[Stellar]

Unfortunately, for the date, time and timestamp macros, it is impossible to specify the exact format to be used.
You can try using the ISO date format "yyyymmdd hh:mi:ss.mmm", in order for SQL statement not to depend on the server settings.

[jjeffman]

It seems it will work.

I have tried it on the SQL Server Studio and it works fine.

Thank you very much.

Best regards.

Jayme Jeffman

[Stellar]

Glad to see that the issue was resolved.
Feel free to contact us if you have any further questions about our products.