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

Question

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.

Answer 1

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.