0

I keep getting an error of

Conversion failed when converting date and/or time from character string.

when I run my query. How should this be altered in order for the query to execute succesfully?

Declare @Table1 Table (name varchar(100), d1 date, amt float)
Insert Into @Table1 (name, d1, amt) Values
('Red', '01/01/2015', '14.00'), ('Red', '01/01/2016', '23.00'),
('Red', '01/03/2015', '22.00'), ('Red', '02/01/2015', '33.00'),
('Red', '03/04/2015', '10,00'), ('Red', '04/04/2016', '10.00'),
('Pink', '01/01/2015', '4.00'), ('Pink', '04/05/2016', '5.00')

DECLARE @ItemList nvarchar(500) = N'''Red''';
DECLARE @startdate date = N'''01/01/2015''';
DECLARE @enddate date = N'''12/31/2015''';

DECLARE @sql nvarchar(max);

SET @sql = N'Select *
FROM OPENQUERY(linkedserver, 
''Select
name
,SUM(CAST(amt As DECIMAL(10,2)) [Total]
FROM @Table1
WHERE name IN ('+ REPLACE(@ItemList, '''', '''''') + N')
    AND d1 BETWEEN ('+ REPLACE(@startdate,'''', '''''') + N')
AND ('+ REPLACE(@enddate,'''', '''''') + N')
''
);
';

EXEC sp_executesql @sql;

EDIT
It is a postgresql database that I am querying, but created a table variable for simplicity and to show DDL

  • Why are you doing this as dynamic sql? – Erik Nov 23 '16 at 16:30
  • Due to the need of using openquery to accomplish it. It is a postgresql database that I am querying, but created a table variable for simplicity and to show DDL – StarsFlyFree FromCozyNights Nov 23 '16 at 16:31
  • 2
    You should definitely stop using ambiguous American date format and format your dates yyyy-mm-dd – Mark Sinkinson Nov 23 '16 at 16:50
  • @MarkSinkinson - even altering the dates to a yyyy/mm/dd format still produces the same error – StarsFlyFree FromCozyNights Nov 23 '16 at 16:54
  • Can't you build the SQL statement separately? If it's possible, it would remove some complexity and make debugging easier. – dezso Nov 23 '16 at 17:05
0

Extra quotes around dates are a problem (as an aside you cannot pass character strings into float columns, which is why your table variable insert also fails).

Using extra quotes can be done at the dynamic layer, and using CHAR(39) rather than embedding a whole load of quotes can make the code a lot more readable don the line.

As an addition, initially selecting out the dynamic SQL so that you can run the generated statement in another window to test your results is also a good thing when starting out.

DECLARE @Table1 TABLE
    (
      name VARCHAR(100) ,
      d1 DATE ,
      amt FLOAT
    );
INSERT  INTO @Table1
        ( name, d1, amt )
VALUES  ( 'Red', '01/01/2015', 14.00 ),
        ( 'Red', '01/01/2016', 23.00 ),
        ( 'Red', '01/03/2015', 22.00 ),
        ( 'Red', '02/01/2015', 33.00 ),
        ( 'Red', '03/04/2015', 10.00 ),
        ( 'Red', '04/04/2016', 10.00 ),
        ( 'Pink', '01/01/2015', 4.00 ),
        ( 'Pink', '04/05/2016', 5.00 );

DECLARE @ItemList NVARCHAR(500) = N'''Red''';
DECLARE @startdate DATE = '01/01/2015';
DECLARE @enddate DATE = '12/31/2015';

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'Select *
FROM OPENQUERY(linkedserver, 
''Select
name
,SUM(CAST(amt As DECIMAL(10,2)) [Total]
FROM @Table1
WHERE name IN (' + REPLACE(@ItemList, '''', '''''') + N')
    AND d1 BETWEEN (' + CHAR(39) + CHAR(39)
    + CAST(@startdate AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
AND (' + CHAR(39) + CHAR(39) + CAST(@enddate AS NVARCHAR(500)) + CHAR(39)
    + CHAR(39) + N')
''
);
';
SELECT @sql;
--EXEC sys.sp_executesql @sql;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.