Conversion failed when converting date and/or time from character string when attempting to use a WHERE clause in an OPENQUERY

Bobby P 221 Reputation points
2022-02-07T15:55:25.353+00:00

I am trying to add the WHERE filtering Clause to my OPENQUERY and am getting the following error...

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

In my original query I have the following defined...

DECLARE @StartDate_In DATE = NULL,
@EndDate_In DATE = NULL
;
--
SET @StartDate_In = '1/22/2022'
;
SET @EndDate_In = '1/28/2022'
;

/* DECLARE Variables */
DECLARE @rayn _StartDateIn DATE,
@rayn _EndDateIn DATE
;

/* Initialize Variables */
-- Utilize @rayn Variables to alleviate Parameter Sniffing
SELECT @rayn _StartDateIn = @StartDate_In,
@rayn _EndDateIn = @EndDate_In
;

DECLARE @StartDateDeriveUTC DATETIME = COALESCE (
DATEADD (
MINUTE,
ABS (DATEPART (
TZ,
CAST(@rayn _StartDateIn AS DATETIME)AT TIME ZONE 'Eastern Standard Time'
)
),
CAST(@rayn _StartDateIn AS DATETIME)AT TIME ZONE 'UTC'
),
'19000101'
)

DECLARE @DynamicSQL NVARCHAR(MAX),
@DynamicQuote CHAR(1) = ''''
;

To try and get @StartDateDeriveUTC in UTC, GMT format in order to query a Microsoft Azure SQL Database

@StartDateDeriveUTC seems to be formatted correctly...2022-01-22 05:00:00.000

Then within the OPENQUERY portion, we have...

INNER JOIN [DynamicsCRM].[dbo].[rsm_status] AS [DynamicsCRM.Email.rsm_notificationtype]
ON [DynamicsCRM.Email.rsm_notificationtype].[Id] = [DynamicsCRM.rsm_emailnotification].[rsm_notificationtype]
WHERE [DynamicsCRM.po_program].[rsm_dtpprogramid] = ' + @DynamicQuote
+ N'''10105''' + @DynamicQuote + N' AND [DynamicsCRM.rsm_emailnotification].[CreatedOn] >= '
+ @DynamicQuote + @DynamicQuote + @StartDateDeriveUTC + @DynamicQuote + @DynamicQuote + N')'
;

Why are we getting this error when we have painstakingly tried to convert DATETIME and with quotes?

Any help would be GREATLY appreciated.

Thanks in advance for all your help.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,566 questions
{count} votes

1 answer

Sort by: Most helpful
  1. LiHong-MSFT 10,046 Reputation points
    2022-02-08T07:01:33.263+00:00

    Hi @Bobby P
    How about change the WHERE filtering Clause

    WHERE [DynamicsCRM.po_program].[rsm_dtpprogramid] = ' + @DynamicQuote  
    + N'''10105''' + @DynamicQuote + N' AND [DynamicsCRM.rsm_emailnotification].[CreatedOn] >= '  
    + @DynamicQuote + @DynamicQuote + @StartDateDeriveUTC + @DynamicQuote + @DynamicQuote + N')'  
    

    to this:

    WHERE [DynamicsCRM.po_program].[rsm_dtpprogramid] =   
    '+ @DynamicQuote + N'''10105''' + @DynamicQuote + N'  AND [DynamicsCRM.rsm_emailnotification].[CreatedOn] >= '  
    + @DynamicQuote +convert(varchar(30), @StartDateDeriveUTC, 109)  + @DynamicQuote + N')'  
    

    Best regards,
    LiHong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments