|
From: Alex M. <ale...@bl...> - 2017-06-29 13:54:39
|
Virtually every MS SQL developer would be confused to a high degree by this format, as it is virtually never used anywhere in MS SQL Server environments. Using JDBC is a tiny niche practice whereas the hugely vast majority use nothing but SSMS. Insisting on forcing this format on them would not be welcome. The setting for specifying date/timestamp formats is already in Squirrel, all that you might want to do is apply it to the output of the generated scripts. Hope that clarifies the confusion. Thank you! Alex From: Neville Rowe [mailto:row...@ho...] Sent: Wednesday, June 28, 2017 5:28 PM To: Alex Malmyguine Cc: squ...@li... Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server Hi Alex, The documents Gerd linked to are the JDBC standard documents as defined by JSR-000221 under the Java Community Process. Those documents define exactly how a JDBC v4.2 driver should operate. According to Microsoft here https://docs.microsoft.com/en-us/sql/connect/jdbc/using-sql-escape-sequences the MS JDBC driver support this escape sequence. We choose to support this in Squirrel since as this is part of the JDBC standard it should work across all JDBC drivers, rather than us having to do something different per driver / database. >> Can you provide an example of a working SQL statement with that as a datetime value, executable in SQL server? When you say ‘executable in SQL server’ - this is a JDBC standard escape sequence, so it is executable in SQL Server so long as the connection to the server is over JDBC. As per the enclosed screenshot of a quick Java test program. Horses for courses - the syntax you had will work on MS SQL Server irrespective of connection type, the syntax SquirrelSQL supports should work on all JDBC connections irrespective of server type. However the problem with your example is the length of the milliseconds - this fails select {ts '2017-02-24 10:56:12.790000’} But this succeeds select {ts '2017-02-24 10:56:12.790'} As per the below screenshot. If we (SquirrelSQL) are putting the extra zero’s in when extracting the value then we’ve got a bug to fix. [cid:image001.jpg@01D2F0BD.B36F0B90] N. On 28 Jun 2017, at 21:04, Alex Malmyguine <ale...@bl...<mailto:ale...@bl...>> wrote: Can you provide an example of a working SQL statement with that as a datetime value, executable in SQL server? Thank you Alex -----Original Message----- From: Alexandre Peshansky [mailto:Ale...@ei...] Sent: Wednesday, June 28, 2017 3:50 PM To: Alex Malmyguine Cc: squ...@li...<mailto:squ...@li...> Subject: RE: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server Is this a more reliable source: https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/date-time-and-timestamp-escape-sequences ? It specifies the same {ts 'yyyy-mm-dd hh:mi:ss.sssss'} as Gerd quoted from Oracle. -- Alexandre Peshansky, MS Lead Bioinformatics Analyst, Research Informatics Core (RIC) Harold & Muriel Block Institute for Clinical & Translational Research at Einstein and Montefiore Albert Einstein College of Medicine 1300 Morris Park Ave, Block Bldg., Rm 534 Bronx, NY 10461 (718) 430-2440, (914) 457-6792 (office) (201) 970-3530 (cell) - NEW!!! -----Original Message----- From: Alex Malmyguine [mailto:ale...@bl...] Sent: Wednesday, June 28, 2017 2:48 PM To: Gerd Wagner; squ...@li...<mailto:squ...@li...> Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server Greg, Why should I look at Oracle docs for MS SQL? Not sure I understand. Obviously, executing this will result in an error in MS SQL: select {ts '2017-02-24 10:56:12.790000'} Msg 241, Level 16, State 3, Line 1 Conversion failed when converting date and/or time from character string. However this works: select '2017-02-24 10:56:12.790' There may be some valid considerations for using the former format, but the statements are generated from MS SQL database, therefore should run in MS SQL correctly whether in Squirrel or any other client. This is not happening - the former format only runs in Squirrel. If I generated a statement in Squirrel, I therefore cannot provide it to another person not using Squirrel, and I cannot save it to source control as no one will be able to apply it. The setting I mentioned should apply to generated timestamps as well. Makes sense? Thank you Alex -----Original Message----- From: Gerd Wagner [mailto:ger...@t-...] Sent: Monday, June 26, 2017 4:30 PM To: Alex Malmyguine; squ...@li...<mailto:squ...@li...> Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server Am 20.06.2017 um 17:08 schrieb Alex Malmyguine: When generating an INSERT-INTO script from the result of a select statement, the DATETIME column values come across as follows: {ts '2017-02-24 10:56:12.790000'} The correct format would be as follows: '2017-02-24 10:56:12.790' No, it isn't. The upper form is correct. See section 13.4.2 of the JDBC specification here: http://download.oracle.com/otndocs/jcp/jdbc-4_2-mrel2-eval-spec/index.html Also, BIT fields come across as 'true' or 'false' which confuses SQL Server when this executes. Can those be changed to 1/0? This should be possible by the help of the MSSQL Plugin. Do you use the plugin. If not please try it out and see if it already works. If not you might want to file a feature request here: https://sourceforge.net/p/squirrel-sql/feature-requests/?source=navbar Gerd ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Squirrel-sql-users mailing list Squ...@li...<mailto:Squ...@li...> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users ------------------------------------------------------------------------------ Check out the vibrant tech community on one of the world's most engaging tech sites, Slashdot.org! http://sdm.link/slashdot _______________________________________________ Squirrel-sql-users mailing list Squ...@li...<mailto:Squ...@li...> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users |
