question

ryan_sql avatar image
ryan_sql asked

USING GETDATE() AS PARAMETER IN SP_EXECUTE SQL

Below is the query it works fine with the below date format

=================

exec sp_executesql N'WITH R AS (SELECT TM.ActivityId, MAX(MS.Date) as Date FROM BAMPrimaryImport.dbo.bam_TransportMessage_Completed AS TM LEFT OUTER JOIN BAMPrimaryImport.dbo.bam_MessageStatus_Completed AS MS ON TM.ActivityID = MS.TransportMessageId WHERE TM.Date >= @fromDate AND TM.Date < @toDate GROUP BY TM.ActivityID)SELECT [Result].[ActivityID], total_count = COUNT(*) OVER() FROM [dbo].[bam_MessageStatus_Completed] as [stat] INNER JOIN [R] as [Result] ON [stat].[TransportMessageId] = [Result].[ActivityID] AND [stat].[Date] = [Result].[Date] WHERE [stat].[Status] = @Status ORDER BY [Result].[date] DESC, (CASE WHEN stat.Status = 5 THEN 1 WHEN stat.Status = 1 THEN 1 ELSE 0 END) OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;',N'@fromDate datetime,@toDate datetime,@Status int',@fromDate='2019-06-21 14:01:04.503',@toDate='2019-07-25 14:01:04.513',@Status=9

but when I want to give the @fromdate as getdate() and @todate as getdate()-7 . (last 7 days) it fails with

Msg 8114, Level 16, State 1, Line 0 Error converting data type varchar to datetime.

declare @startdate as Datetime = Getdate()

declare @enddate as Datetime = Getdate()-7

exec sp_executesql N'WITH R AS (SELECT TM.ActivityId, MAX(MS.Date) as Date FROM BAMPrimaryImport.dbo.bam_TransportMessage_Completed AS TM LEFT OUTER JOIN BAMPrimaryImport.dbo.bam_MessageStatus_Completed AS MS ON TM.ActivityID = MS.TransportMessageId WHERE TM.Date >= @fromDate AND TM.Date < @toDate GROUP BY TM.ActivityID)SELECT [Result].[ActivityID], total_count = COUNT(*) OVER() FROM [dbo].[bam_MessageStatus_Completed] as [stat] INNER JOIN [R] as [Result] ON [stat].[TransportMessageId] = [Result].[ActivityID] AND [stat].[Date] = [Result].[Date] WHERE [stat].[Status] = @Status ORDER BY [Result].[date] DESC, (CASE WHEN stat.Status = 5 THEN 1 WHEN stat.Status = 1 THEN 1 ELSE 0 END) OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY;', N'@fromDate datetime,@toDate datetime,@Status int', @fromDate='@startdate',@toDate='@enddate',@Status=9

@fromDate datetime,@toDate datetime,@Status int', @fromDate='@startdate',@toDate='@enddate',@Status=9

if i do @startdate without single quotes script runs fine without syntax errors but no results

sql-server-2012sql server 2014
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ryan_sql avatar image
ryan_sql answered

i am able to fix the issue please mark this post as complete

1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you supply how you resolved the issue? Others might have the same issue and would be looking for an answer. Thanks.

0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.