Search code examples
mysqldatesql-server-2012ssrs-2012bids

Why I get different date format for same SQL run on linked MySQL server and Query Designer of BIDS

I have created a linked MySQL server on MS SQL Server Management Studio. When I run following SQL from Management Studio it gives date format same as in the MySQL table:

SELECT
*
FROM OPENQUERY (
    LinkedServerName,
    'select distinct date from DatabaseName.Tablename order by date desc limit 10;'
);

Result is:

date
2017-06-28
2017-06-27
2017-06-26
2017-06-25
2017-06-24
2017-06-23
2017-06-22
2017-06-21
2017-06-20
2017-06-19

This is consistent with definition of date field in MySQL table, which is: enter image description here

But when I run same SQL from Query Designer of Business Intelligence Development Studio I get date field in different format.

The result of same SQL from BIDS is:

enter image description here

Is there a way I can modify my SQL so result in BIDS shows date format as yyyy-mm-dd?

Solution

  • I was little impatient! I was able to get it working using:

    SELECT
*
FROM OPENQUERY (
    LinkedServerName,
    'select distinct DATE_FORMAT(date, ''%Y-%m-%d'') from DatabaseName.Tablename order by date desc limit 10;'
);

    I wanted to keep the field in date format and hence didn't want to change it as varchar. The above approach serves that as well.