Sql-server – Conversion failed when converting from a character string to uniqueidentifier

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...

You are storing dates as strings - why? Opening_Date and Date should be date or datetime, not varchar. But before you can fix that, you need to identify the rows that are causing the conversion problem:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE ISDATE([Date]) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE ISDATE(Opening_Date) = 0;

And now that you've fixed the question and I know you're using 2012 and not 2008 R2, it might be cleaner to use TRY_CONVERT(), especially since it will allow you to identify any rows where the month and day are transposed incorrectly. For example, assuming you expect dates to be stored as mm/dd/yyyy strings:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE TRY_CONVERT(datetime, [Date], 101) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE TRY_CONVERT(datetime, Opening_Date, 101) = 0;

In addition to identifying garbage rows where users have stored nonsense like floob and 9992-13-36 as "dates," this will also identify rows where users have stored 13/07/1999 instead of 07/13/1999 (but there is no way to know if 05/06/2000 is meant to be May 6 or June 5).

Now you need to fix those rows before you can correct the tables.

ALTER TABLE dbo.DimStore ALTER COLUMN Opening_Date date; -- or datetime;
ALTER TABLE dbo.FactTransactions ALTER COLUMN [Date] date; -- or datetime;

You might also consider renaming the Date column to be (a) less vague and (b) not a reserved word.

If you can't fix the tables, then you need to change your query:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY, 
  CASE WHEN ISDATE(T2.Opening_Date) = 1 THEN T2.OpeningDate END,
  CASE WHEN ISDATE(T1.[Date]) = 1 THEN T1.Date END)
FROM dbo.FactTransactions AS T1
INNER JOIN dbo.DimStore AS T2 
  ON T1.cod_store = T2.cod_storeKey
WHERE ISDATE(T2.Opening_Date) = 1
  AND ISDATE(T1.[Date]) = 1;

And @RLF brought up a great point, too; if you can't fix the table, then the date columns could contain data that represent a specific date (say, September 7) but be entered in the wrong format (e.g. on a US English system, entered as a string in British format, 7/9/2015). So really, you need to fix the table and stop storing these things as strings.

Some other useful material:

• Bad habits to kick : mis-handling date / range queries
• Bad habits to kick : avoiding the schema prefix