Automatic Timestamp convesrion

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
jjeffman
Posts: 79
Joined: Tue 09 Nov 2004 12:22
Location: Porto Alegre-Rio Grande do Sul - Brazil

Automatic Timestamp convesrion

Post by jjeffman » Wed 28 Nov 2018 20:21

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
Posts: 79
Joined: Tue 09 Nov 2004 12:22
Location: Porto Alegre-Rio Grande do Sul - Brazil

Re: Automatic Timestamp convesrion

Post by jjeffman » Thu 29 Nov 2018 13:25

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
Posts: 79
Joined: Tue 09 Nov 2004 12:22
Location: Porto Alegre-Rio Grande do Sul - Brazil

Re: Automatic Timestamp convesrion

Post by jjeffman » Thu 29 Nov 2018 14:21

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
Devart Team
Posts: 179
Joined: Tue 03 Oct 2017 11:00

Re: Automatic Timestamp convesrion

Post by Stellar » Tue 04 Dec 2018 09:42

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
Posts: 79
Joined: Tue 09 Nov 2004 12:22
Location: Porto Alegre-Rio Grande do Sul - Brazil

Re: Automatic Timestamp convesrion

Post by jjeffman » Tue 04 Dec 2018 12:59

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
Devart Team
Posts: 179
Joined: Tue 03 Oct 2017 11:00

Re: Automatic Timestamp convesrion

Post by Stellar » Wed 05 Dec 2018 16:02

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

Post Reply