If the date/time/timestamp format is [None], use "yyyy-MM-dd HH:mm:ss.SSS" pattern and UTC time zone. This means not to apply any time zone adjustment to the values. ADS displays the exact values as returned from the database server.

This applies to all databases, including MongoDB, BigQuery, Cassandra, SQL Server, etc.

SQL Server scenario from customer:

select [SYSDATETIMEOFFSET()] = SYSDATETIMEOFFSET(), [SYSDATETIME()] = SYSDATETIME(), [SYSUTCDATETIME()] = SYSUTCDATETIME(), [GetDate()] = GetDate(), [GetUTCDate()] = GetUTCDate()

To display the values as in SQL Management Studio, set the Results Format as follows:

- Data type DATETIMEOFFSET, set format to yyyy-MM-dd HH:mm:ss.SSS Z
- Data type DATETIME, set format to [None]
- Data type DATETIME2, set format to [None]

I working on this issue, I have completed with Cassandra,Mongo DB,Apache Derby and Sybase Anywhere.

Go to File -> Options -> Result Format -> Select Sybase Anywhere -> Select Data type as Date ->Select Format as NONE

Go to File -> Options -> Result Format -> Select Apache Derby -> Select Data type as DATE ->Select Format as NONE

Umesh, when I try your scenarios, both Sybase Anywhere and Apache Derby return the same results, i.e. both results have a time value, if I set the Results Format to [None]. But you said that Sybase Anywhere returns only a date value but Apache Derby returns a date/time value. Please provide a screenshot that shows the results and the Results Format settings.

Hi Jenny ,

It was my bad , Sybase Anywhere show correct Output.
Please refer attached "Anywhere_Date.png".
I am performing test with Date, Date Time,Time and Time stamp datatypes.
Please let me know weather we need to test for other date data types i.e Small Date time, Date Time Offset, time stamp with timezone etc. [SP] Yes, we need to perform the tests on these data types as well

Umesh, please use build 17.0.3-11 to verify this issue. I made the following changes to the format patterns for [None]:

We are not getting expected result for Azure DB.

Please consider some scenarios in Azure.sql and also refer Azure.png

Actually, the problem with Azure DB is not caused by the fix for this issue. Results format for Azure DB doesn't work at all. I notice that the File -> Options -> Results Format dialog for Azure doesn't function correctly.

1. For "MS SQL Database (Azure)", choose INT data type, set format to #,##0.
2. Now choose DATE data type, format stays as #,##0. It should be the current format for DATE data type.
3. Save changes and close the File -> Options dialog.
4. Examine the datastudio.properties file. You can't find any entry for "azure.format.datatype". As a result, the format for all Azure data types is always [Default].

Umesh, please log a new issue for this Azure problem.

@umesh: can you make a list of all missing datatypes and DB and sent me an email ? I will look into this and discuss with Ivan to see what needs to be added.

The scenario in this comment is working correctly.

First, you should verify the data type of the fields by using the schema browser.
- For the mydate1 collection, col1 has "String" data type and col2 has "BSONTimestamp" data type (which is for internal MongoDB use).
- For the mydate2 collection, col1, col2, and col3 have "Date" data type.

DATE format applies to fields with "Date" data type only.

Here is the output from MongoDB CLI. Note that the fields in mydate1 are displayed in a different format than the fields in mydate2.

rs0:PRIMARY> db.mydate1.find()
{ "_id" : ObjectId("56ec3a3044acedd5c104eb5e"), "col1" : "Fri Mar 18 2016 10:26:08 GMT-0700 (PDT)", "col2" : Timestamp(1458321968, 2) }

rs0:PRIMARY> db.mydate2.find()
{ "_id" : ObjectId("56ec3ac144acedd5c104eb61"), "col1" : ISODate("2016-03-18T17:28:33.200Z"), "col2" : ISODate("2016-03-18T17:28:33.200Z"), "col3" : ISODate("2016-03-18T17:28:33.200Z") }

Verifierd  in ADS v17.  I have updatated all tests here

I've reopened this issue so that we can do regression based on #14589 results.

Initially, in this issue comment, a customer reported a problem with the following query in SQL Server.

select [SYSDATETIMEOFFSET()] = SYSDATETIMEOFFSET(), [SYSDATETIME()] = SYSDATETIME(), [SYSUTCDATETIME()] = SYSUTCDATETIME(), [GetDate()] = GetDate(), [GetUTCDate()] = GetUTCDate()

This was actually a bug with SYSDATETIMEOFFSET, but at that time we thought it was caused by UTC adjustment and made a change to return the values in UTC time zone.  The UTC fix was actually a workaround for the SYSDATETIMEOFFSET bug.

Recently, another customer reported a problem with the following query in SQL Server.

select getdate() AS CurrentDateTime, getutcdate() AS CurrentUTCDateTime

The above query doesn't call SYSDATETIMEOFFSET and therefore it doesn't encounter the SYSDATETIMEOFFSET bug.  However, the initial SYSDATETIMEOFFSET workaround now causes a problem with GETDATE and GETUTCDATE queries that don't call SYSDATETIMEOFFSET.

We have made the following changes to address both problems:

- Fixed the SYSDATETIMEOFFSET bug in jTDS
- Reverted the workaround that set the result format using the UTC time zone.  SQL Server date/time values don't require this adjustment.

Using ADS 17.0.7-7, the two queries above should return all the date/time values in UTC time zone when the result format is [None] and all the date/time values returned from the two queries should be identical.

In addition, we've checked all the other databases and their date/time data types.  I've updated the ConnectionMetaData.useUTCFormatOnNONEDateFormat method based on the query results in issue #14589.

For v17, Verified in ADS 17.0.7-7.  We have added screenshots of all databases here.  

Verified in V18(ADS 18.0.0-devi-207/ADS 18.0.0-devi-211). Updated screenshots here for all DBs(Not covered for DB2-z/OS)

Verified in ADS 17.0.11-5 and ADS 18.0.0-preview-36 for oracle 12c and Mysql 5.7. So marking issue as closed.