Date/time format in the generated insert into script for SQL Server

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Date/time format in the generated insert into script for SQL Server

Alex Malmyguine-2

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'

 

In the settings I can see that the above format is supposed to be used for timestamps in WHERE clauses, but it seems to be used for datetime of the VALUES clause as well.

Changing the setting below does not seem to have any impact on the VALUES.

 

 

Also, BIT fields come across as 'true' or 'false' which confuses SQL Server when this executes. Can those be changed to 1/0?

 

Thank you!

Alex

 

SQuirreL SQL Client Version 3.7.1


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Gerd Wagner-3


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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alex Malmyguine-2
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:[hidden email]]
Sent: Monday, June 26, 2017 4:30 PM
To: Alex Malmyguine; [hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alexandre Peshansky
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:[hidden email]]
Sent: Wednesday, June 28, 2017 2:48 PM
To: Gerd Wagner; [hidden email]
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:[hidden email]]
Sent: Monday, June 26, 2017 4:30 PM
To: Alex Malmyguine; [hidden email]
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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alex Malmyguine-2
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:[hidden email]]
Sent: Wednesday, June 28, 2017 3:50 PM
To: Alex Malmyguine
Cc: [hidden email]
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:[hidden email]]
Sent: Wednesday, June 28, 2017 2:48 PM
To: Gerd Wagner; [hidden email]
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:[hidden email]]
Sent: Monday, June 26, 2017 4:30 PM
To: Alex Malmyguine; [hidden email]
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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alexandre Peshansky
SELECT *  
  FROM [Dental].[dbo].[APR2010] WHERE [HIRE DATE] > {ts '2010-03-31 00:00:00.000'}

-- 
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:[hidden email]]
Sent: Wednesday, June 28, 2017 4:05 PM
To: [hidden email]
Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server

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:[hidden email]]
Sent: Wednesday, June 28, 2017 3:50 PM
To: Alex Malmyguine
Cc: [hidden email]
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:[hidden email]]
Sent: Wednesday, June 28, 2017 2:48 PM
To: Gerd Wagner; [hidden email]
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:[hidden email]]
Sent: Monday, June 26, 2017 4:30 PM
To: Alex Malmyguine; [hidden email]
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
[hidden email]
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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

John Hardin
On Wed, 28 Jun 2017, Alexandre Peshansky wrote:

> SELECT *
>  FROM [Dental].[dbo].[APR2010] WHERE [HIRE DATE] > {ts '2010-03-31 00:00:00.000'}

Or even just:

    SELECT {ts '2010-03-31 00:00:00.000'};

...works. Learn something new every day...

However, I too get this result:

> 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.

...which, along with getting errors in Squirrel from generated INSERT
statements, supported my assumption that the {ts} format was an annoying
JDBC-ism.

As it turns out, MSSQL only allows three decimal places in the fractional
time part. This works:

   SELECT {ts '2017-02-24 10:56:12.790'}

...so it isn't *exactly* as documented by 'Orrible for JDBC or MSFT for
ODBC.

Perhaps Squirrel needs to be made aware of the decimal places limit so
that it can generate working timestamps when querying from MSSQL?


--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   USMC Rules of Gunfighting #7: In ten years nobody will remember
   the details of caliber, stance, or tactics. They will only remember
   who lived.
-----------------------------------------------------------------------
  6 days until the 241st anniversary of the Declaration of Independence

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Neville Rowe
In reply to this post by Alexandre Peshansky
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 the example is the length of the final field - this fails

select {ts '2017-02-24 10:56:12.790000’}

But this succeeds

select {ts '2017-02-24 10:56:12.790'}

There is nothing in the spec I can find which talks about the length of that field so this is probably debatable. I’ve always thought of it as milliseconds but I guess this is coming out as microseconds.

N.


> On 28 Jun 2017, at 21:44, Alexandre Peshansky <[hidden email]> wrote:
>
> SELECT *  
>  FROM [Dental].[dbo].[APR2010] WHERE [HIRE DATE] > {ts '2010-03-31 00:00:00.000'}
>
> --
> 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:[hidden email]]
> Sent: Wednesday, June 28, 2017 4:05 PM
> To: [hidden email]
> Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server
>
> 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:[hidden email]]
> Sent: Wednesday, June 28, 2017 3:50 PM
> To: Alex Malmyguine
> Cc: [hidden email]
> 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:[hidden email]]
> Sent: Wednesday, June 28, 2017 2:48 PM
> To: Gerd Wagner; [hidden email]
> 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:[hidden email]]
> Sent: Monday, June 26, 2017 4:30 PM
> To: Alex Malmyguine; [hidden email]
> 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
> [hidden email]
> 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
> [hidden email]
> 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
> [hidden email]
> 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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alex Malmyguine-2
In reply to this post by Alexandre Peshansky
I could not help but noticed that you modified the timestamp, versus generated by Squirrel, ever so slightly: yours only contains 3 digits for milliseconds.
Converting your statement to an MWE, I can get it to work, but if I add back 3 extra digits for milliseconds, as Squirrel generates, I get an error again. Run this:

declare @APR2010 table ([hire date] datetime)

SELECT *
  FROM @APR2010 WHERE [HIRE DATE] > {ts '2010-03-31 00:00:00.000'} -- yours

SELECT *
  FROM @APR2010 WHERE [HIRE DATE] > {ts '2010-03-31 00:00:00.000000'} -- Squirrel's

There needs to be a setting for output format of timestamps, as not everyone wants the ODBC TS format, and even searching/replacing in the generated scripts is a tedious task. I assume some code is already roughed in where my screen shot was showing, it only needs to be enhanced to apply to generated outputs.

Thank you!
Alex

-----Original Message-----
From: Alexandre Peshansky [mailto:[hidden email]]
Sent: Wednesday, June 28, 2017 4:44 PM
To: Alex Malmyguine
Cc: [hidden email]
Subject: RE: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server

SELECT *
  FROM [Dental].[dbo].[APR2010] WHERE [HIRE DATE] > {ts '2010-03-31 00:00:00.000'}

--
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:[hidden email]]
Sent: Wednesday, June 28, 2017 4:05 PM
To: [hidden email]
Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server

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:[hidden email]]
Sent: Wednesday, June 28, 2017 3:50 PM
To: Alex Malmyguine
Cc: [hidden email]
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:[hidden email]]
Sent: Wednesday, June 28, 2017 2:48 PM
To: Gerd Wagner; [hidden email]
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:[hidden email]]
Sent: Monday, June 26, 2017 4:30 PM
To: Alex Malmyguine; [hidden email]
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
[hidden email]
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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alex Malmyguine-2
In reply to this post by Alex Malmyguine-2

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:[hidden email]]
Sent: Wednesday, June 28, 2017 5:28 PM
To: Alex Malmyguine
Cc: [hidden email]
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

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.

 

 

N.

 

 

On 28 Jun 2017, at 21:04, Alex Malmyguine <[hidden email]> 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 [[hidden email]]
Sent: Wednesday, June 28, 2017 3:50 PM
To: Alex Malmyguine
Cc: [hidden email]
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 [[hidden email]]
Sent: Wednesday, June 28, 2017 2:48 PM
To: Gerd Wagner; [hidden email]
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 [[hidden email]]
Sent: Monday, June 26, 2017 4:30 PM
To: Alex Malmyguine; [hidden email]
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
[hidden email]
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
[hidden email]
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

John Hardin
On Thu, 29 Jun 2017, Alex Malmyguine wrote:

> 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.

Counterargument: while the {ts} format is unexpected, it *does* work
(assuming the decimal places are limited), and (this is the important bit)
it has the benefit of being robust in the face of different locales' date
formats.

If you're generating locale-specific-formatted date data in SQL, sure it
will work when *you* run that SQL, but relying it to work always for
everyone will eventually come back to bite you.

Yes, I've been badly bitten by this assumption in the past.

So there might need to be a separate date option for generated SQL,
offering localized date formats or {ts} format with specified decimal
places, or a locale-independent common format (e.g. ISO 8601).

--
  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/
  [hidden email]    FALaholic #11174     pgpk -a [hidden email]
  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
   ...every time I sit down in front of a Windows machine I feel as
   if the computer is just a place for the manufacturers to put their
   advertising.                                 -- fwadling on Y! SCOX
-----------------------------------------------------------------------
  5 days until the 241st anniversary of the Declaration of Independence

------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Date/time format in the generated insert into script for SQL Server

Alex Malmyguine-2

Yeah, and that is why the settings option was probably put in place: to let those who like ODBC format use it, and let the rest use MS format.

It just should apply to generated script output, that's it. But currently both highlighted seem to have no effect:

 

 

Thank you!

Alex

 

-----Original Message-----
From: John Hardin [mailto:[hidden email]]
Sent: Thursday, June 29, 2017 4:47 PM
To: Alex Malmyguine
Cc: [hidden email]
Subject: Re: [Squirrel-sql-users] Date/time format in the generated insert into script for SQL Server

 

On Thu, 29 Jun 2017, Alex Malmyguine wrote:

 

> 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.

 

Counterargument: while the {ts} format is unexpected, it *does* work (assuming the decimal places are limited), and (this is the important bit) it has the benefit of being robust in the face of different locales' date formats.

 

If you're generating locale-specific-formatted date data in SQL, sure it will work when *you* run that SQL, but relying it to work always for everyone will eventually come back to bite you.

 

Yes, I've been badly bitten by this assumption in the past.

 

So there might need to be a separate date option for generated SQL, offering localized date formats or {ts} format with specified decimal places, or a locale-independent common format (e.g. ISO 8601).

 

--

  John Hardin KA7OHZ                    http://www.impsec.org/~jhardin/

  [hidden email]    FALaholic #11174     pgpk -a [hidden email]

  key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C  AF76 D822 E6E6 B873 2E79

-----------------------------------------------------------------------

   ...every time I sit down in front of a Windows machine I feel as

   if the computer is just a place for the manufacturers to put their

   advertising.                                 -- fwadling on Y! SCOX

-----------------------------------------------------------------------

  5 days until the 241st anniversary of the Declaration of Independence


------------------------------------------------------------------------------
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
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Loading...