Well I find myself again the dummie of the Internet.  I basically came across this error and STFW'd for ages following complex T-SQL Timestamp conversions but to no avail.  Not saying that you shouldn't try their solutions but just check you haven't done this silly mistake.

I'm only guessing the same error would happen in Report Builder 2.0 which I've stopped using as my day job wants us to use Business Intelligence Development Studio for all our SQL Server Analysis Service (SSAS) and Reporting Service (SSRS) projects/solutions.  The features and interface are very similar when developing reports though.

The error I'd get was

An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the dataset DataSet1.
Conversion failed when converting date and/or time from character string.

Source: http://www.kodyaz.com/articles/sql-format-date-format-datetime-t-sql-convert-function.aspx

My setup

  • Windows XP SP3
  • MS SQL Server Management Studio 2008
  • SQL Server 2008 R2
  • Regional Setting: London BST (British Summer Time)

I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:

  1.  SELECT 
  2.  DATENAME(dw, StartDate) AS 'Day' 
  3.  FROM  
  4.  Timetable 
  5.  ORDER BY 
  6.  StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC 
  8.  -- Yields 
  9.  /* 
  10.  Day               
  11.  ----------------  
  12.  Monday 
  13.  Monday 
  14.  Monday 
  15.  Tuesday 
  16.  Tuesday 
  17.  Wednesday 
  18.  Thursday 
  19.  Thursday 
  20.  Thursday 
  21.  Thursday 
  22.  Friday 
  23.  Friday 
  24.  */