StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL query returns different results when run from Excel
text
Body
copied!<p>A have a query that returns different results when run directly in SQL Server Management Studio and when run from Excel (using the SQLOLEDB provider). Both return a table with 12 rows (one per month) and two columns (date and a complex count), but some of the numeric values are incorrect in the Excel result set.</p> <p>I'm happy to show you the full detail, but to start with can you suggest what type of things can cause such a difference?</p> <p>Versions: SQL Server 10.50.4000, Excel 2013</p> <p>Edit: the full details ...</p> <pre><code>select cast(ActualDate as date) as 'Start of rolling 12 months' , count(distinct x.con_id) as 'Total Sponsors' from DateTimeDimV4.dbo.datedim join (select cm_id, con_id , cm.start_date as activation_date , isnull(stop_date, getdate()) as stop_date from cm join con_act ca on ca.con_act_db_id = cm.con_act_db_id and ca.con_act_id = cm.con_act_id where ca.code = 'corres' ) X on cast(activation_date as date) <= cast(ActualDate as date) and cast(stop_date as date) > cast(ActualDate as date) where --ActualDate = eomonth(ActualDate) -- only works in SQL 2012, so instead we use ... ActualDate = DATEADD(dd, -DAY(DATEADD(mm, 1, ActualDate)), DATEADD(mm, 1, ActualDate)) and ActualDate >= '31 Jul 2012' and ActualDate <= '30 June 2013' group by cast(ActualDate as date) order by cast(ActualDate as date) </code></pre> <p>In SSMS, this returns ...</p> <pre><code>Start of rolling 12 months Total Sponsors 2012-07-31 862 2012-08-31 872 2012-09-30 872 2012-10-31 880 2012-11-30 876 2012-12-31 878 2013-01-31 882 2013-02-28 888 2013-03-31 887 2013-04-30 887 2013-05-31 920 2013-06-30 933 </code></pre> <p>But in Excel I get ...</p> <pre><code>Start of rolling 12 months Total Sponsors 2012-07-31 862 2012-08-31 872 2012-09-30 872 2012-10-31 880 2012-11-30 876 2012-12-31 878 2013-01-31 882 2013-02-28 887 2013-03-31 887 2013-04-30 887 2013-05-31 887 2013-06-30 887 </code></pre> <p>Note that the first seven rows are identical but that Excel repeats the same incorrect value in the last five rows. </p> <p>Also note that if I change the literal dates from '31 Jul 2012' and '30 June 2013' to '31 Jul 2011' and '30 June 2012' then the two environments produce results that are identical to each other.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload