Datepart hour is not supported by date function Dateadd for data type

[David Dickey]

I have the following SQL in the report designer as code, not generated by the graphical tool, which looks like the following:

Declare @BeginDateEx DATE

Declare @EndDateEx DATE

Declare @TimeZoneOffSetEx INT

SET @ReportIdEx = @ReportId

SET @BeginDateEx = @BeginDate

SET @EndDateEx = @EndDate

SET @TagTokenEx = @TagToken

SET @TimeZoneOffSetEx = @TimeZoneOffSet

Select D.*,

DG.DisplayGroupName,

        dbo.udf_GetNumeric(D.TagName) AS ZoneNumber,

        DateAdd("HH", @TimeZoneOffSetEx-24+3, D.TimestampUTC) LocalDateTime,

        Row_Number() Over(Partition By D.ProcessId, D.TagName, Cast(DateAdd("HH", @TimeZoneOffSetEx-24+3, D.TimestampUTC) as Date) Order By D.TimestampUTC) rn

From pcc.AnalogTagDataSummary D

Inner Join rpt.BaseReportData B

ON D.ProcessId = B.ProcessId

Inner Join rpt.DisplayGroupTags DG

ON B.ReportId = DG.ReportId

AND D.TagName = DG.TagName

AND DG.DisplayGroupType = 'Summary' 

 --Where D.TimestampUTC Between DateAdd("HH", -@TimeZoneOffSetEx, @BeginDateEx) AND DateAdd("HH", -@TimeZoneOffSetEx, DateAdd("HH", 1, @EndDateEx))

 Where D.TimestampUTC Between DateAdd("HH", -@TimeZoneOffSetEx, @BeginDateEx) AND '10/2/2020'

AND B.ReportId = @ReportIdEx

AND DG.DisplayGroupName = @TagTokenEx

This query works when run in SSMS but doesn't work when run the bold reports designer tool.  I receive the following error when attempting to run the query:

Sf_Exception - System.Exception: The datepart hour is not supported by date function dateadd for data type date. at BoldReports.RDL.Data.SqlDataExtension.GetData(String& error) at BoldReports.Web.ReportDesigner.DataProvider.GetTableData() at BoldReports.Web.ReportDesigner.Internal.ReportDesignerInternalHelper.ProcessDesigner()

The query seems to failing on the @BeginDateEx because apparently it doesn't like the data type (which has previously been declared as Date).  I've tried various things but nothing seems to work.  Is this supported?  Am I missing something syntactically?  I couldn't seem to find any examples out there that did something similar.  Any help you can provide would be appreciated.

[Muthuramana Sankaranarayanan]

Hi David, 

 

Thanks for your interest in Bold Reports. 

 

We will check the reported issue from our end and provide you further details for this on October 5^th, 2020. 

 

Regards,
Muthu Ramana S 

[Muthuramana Sankaranarayanan]

Hi David, 

 

We checked the reported issue from our end but we were not able to reproduce it. We will further check this issue and provide you details on tomorrow. 

 

Regards, 

Muthu Ramana S 

[Muthuramana Sankaranarayanan]

Hi David, 

We checked the reported issue and suspect that this issue occurs in query level when using with SQL. We suggest you to refer and use the suggestion provided in below link to get the issue resolved, 

https://stackoverflow.com/questions/14957491/the-datepart-hour-is-not-supported-by-date-function-dateadd-for-data-type-date 

 

Regards, 

Muthu Ramana S