#15354: View as spreadsheet displays a different date format

tariqrahiman reported 2017-06-27T01:08:53Z · last modified 2017-08-19T20:39:13Z

View as spreadsheet displays a different date format

Dev	JennyNishimura
QA	kin-hong

Priority	Low
Complexity	Unknown

Component	DB - Excel
Version	18.0

Version: 18.0.17

Build #: 54878

Build Date: 2017-Jun-26 07:31:47 AM

View as spreadsheet displays a different date format

To reproduce:

Pre-requisites:

Go to File menu->options

Go to Result format option and select SQL Server database

Select datatypes->Date and select format "$#,##0.00;($#,##0.00)". Do the same for Datetime & Timestamp

Click on ok button

1. Logon to AWS SQL Server v2008 R2 at 1.71 and navigate to AdventureWorksDW and perform

SELECT * FROM [dbo].[i15]

2. Click on View as Spreadsheet icon on the results grid.

3. You can see that the output displays May 09, 2017 for Date fields

4. The format of output to Export to Excel was 9/05/2017 up to 18.0.13-7

See attached screenshot

Expected View as Spreadsheet for Date datatypes should be 9/05/2017 instead of May 09, 2017

SQL Script

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

CREATE TABLE [dbo].[i15] (

[int_col] int NOT NULL,

[date_col] date NOT NULL,

[datetime_col] datetime NOT NULL,

[time] timestamp NOT NULL

)

INSERT INTO [dbo].[i15]([int_col], [date_col], [datetime_col], [time])

VALUES(0, '2017-05-09', '20170509 10:45:31.0', '00000000000007d2')

INSERT INTO [dbo].[i15]([int_col], [date_col], [datetime_col], [time])

VALUES(1, '2017-05-09', '20170509 10:45:31.0', '00000000000007d3')

3 attachments

Screen Shot 2017-06-26 at 6.17.45 pm.png

2017-06-27T01:19:33Z

260 KB
Screen Shot 2017-06-26 at 6.30.45 pm.png

2017-06-27T01:31:09Z

233 KB
Issue15354.png

2017-08-09T12:23:16Z

251 KB

All Comments (7) Change History

JennyNishimura 2017-06-27T03:35:24Z

"$#,##0.00;($#,##0.00)" is an invalid format for date and timestamp values.

Have you tried valid formats? Or [Default] or [None]? What are the results in those cases?

tariqrahiman 2017-06-27T03:43:52Z

valid formats work fine. But if we look at older ADS v17 builds, it does not follow the same pattern

JennyNishimura 2017-08-04T21:19:15Z

This is now fixed. Please do regression testing with valid and invalid formats.

dutondeumesh 2017-08-09T12:23:55Z

Verified in ADS 19.0.0-beta-37

tariqrahiman 2017-08-09T21:40:02Z

Verified in ADS v19.0.0-beta-37

JennyNishimura 2017-08-17T04:56:28Z

I've backported the fix for this issue to v18 due to the problem reported in this issue comment.

tariqrahiman 2017-08-19T20:39:13Z

Verified in 18.0-18-5 and 19.0.0-beta-34

Search Tips

Aqua Data Studio / nhilam

View as spreadsheet displays a different date format

3 attachments

Issue #15354

Completion

Search Tips

Aqua Data Studio / nhilam

Title

View as spreadsheet displays a different date format

3 attachments

Issue #15354

Completion