1

Topic: Prompt by mistake in dynamic request

Here request:
DECLARE @sql nvarchar (1000)
DECLARE @date date
SET @sql =
' SELECT Account.account_number, currency, amount, CAST (date_time_operation AS DATE) AS date_bank_operation, date_time_operation
FROM Account
JOIN Bank_operations ON Account.account_number = Bank_operations.account_number
WHERE currency = "EUR"
AND CAST (date_time_operation AS DATE) = "d nvarchar (20)"
AND Account.account_number = 10000011'
SET @date = ' @d nvarchar (20)'
EXECUTE sp_executesql @sql, @date, @d = ' 2017-01-17'
Produces an error - Conversion failed when converting date and/or time from character string.
Where at me an error and how it to correct?

2

Re: Prompt by mistake in dynamic request

cglcz;

AND CAST (date_time_operation AS DATE) = "d nvarchar (20)"

You try to compare date_time_operation with a line @d nvarchar (20)
Replace on

AND CAST (date_time_operation AS DATE) = @d

3

Re: Prompt by mistake in dynamic request

cglcz;

DECLARE @date date
...
SET @date = ' @d nvarchar (20)'

???

4

Re: Prompt by mistake in dynamic request

HandKot;
It is impossible, all the same the same error

5

Re: Prompt by mistake in dynamic request

cglcz;
It is impossible that? To you show that you wrote bosh.

6

Re: Prompt by mistake in dynamic request

HandKot;
Everything, turned out. Thanks.
GO
DECLARE @sql nvarchar (1000)
DECLARE @date nvarchar (20)
SET @sql =
' SELECT Account.account_number, currency, amount, CAST (date_time_operation AS DATE) AS date_bank_operation, date_time_operation
FROM Account
JOIN Bank_operations ON Account.account_number = Bank_operations.account_number
WHERE currency = "EUR"
AND CAST (date_time_operation AS DATE) = @d
AND Account.account_number = 10000011'
SET @date = ' @d nvarchar (20)'
EXECUTE sp_executesql @sql, @date, @d = ' 2017-01-17'

7

Re: Prompt by mistake in dynamic request

cglcz;

DECLARE @sql nvarchar (1000)
SET @sql ='
SELECT Account.account_number, currency, amount, CAST (date_time_operation AS DATE) AS date_bank_operation, date_time_operation
FROM Account
JOIN Bank_operations ON Account.account_number = Bank_operations.account_number
WHERE currency = "EUR"
AND date_time_operation> = @d
AND date_time_operation <DATEADD (DAY, 1, @d)
AND Account.account_number = 10000011'
EXECUTE sp_executesql
@sql,
-- To replace type @d with column data type date_time_operation
N ' @d DATETIME ';
-- At assignment it is better to variables of type DATE/DATETIME/DATETIME2 to use ISO a format:
-- Short date - 
-- Date and time - --T::
-- @d = ' 20170117'
-- @d = ' 2017-01-17T00:00:00'
@d = ' 20170117'