topic Re: SQL Date format not recognized in Paginated report in Report Server

SQL Date format not recognized in Paginated report

Riskworks114 — Mon, 16 Sep 2024 16:35:32 GMT

This is interesting.

In a paginated report the format string convert(VARCHAR (10),ThruDt , 112) is not working when the query includes a physical table. When I eliminated the physical table the format string worked. The query context is not complex, and should not be confusing the optimizer. I have not tried to replicate this with an AdventureWorks database, as we don't have on in our Dev environment. SQL Server 2022.

There is a workaround, but any suggestions? This doe not seem to be a known issues as of now. Thanks.

DECLARE getdate DATE

SET getdate = GETDATE()

IF Object_Id('tempdb.dbo.#gp') IS NOT NULL DROP TABLE #gp

select YEAR1, PERIODID , @ThruDt AS 'ThruDt' , getdate AS 'GetDate'

into #gp

from (

select b.YEAR1,

b.PERIODID

from MyTable

WHERE B.YEAR1 = @Year

AND B.PERIODID = @Period

) v

select ThruDt ,

convert(VARCHAR (10),ThruDt) AS 'StrThruDt',

convert(VARCHAR (10),ThruDt , 112) AS 'StrThruDt_112',

convert(VARCHAR (10),GETDATE() , 112) AS 'StrGetDate__112',

convert(VARCHAR (10), ThruDt, 126) AS 'StrThruDt_126',

CONVERT(VARCHAR(4), YEAR(ThruDt)) +

RIGHT('0' + CONVERT(VARCHAR(2), MONTH(ThruDt)), 2) +

RIGHT('0' + CONVERT(VARCHAR(2), DAY(ThruDt)), 2) AS Period_Concatinated

from #gp

Re: SQL Date format not recognized in Paginated report

parry2k — Mon, 16 Sep 2024 17:07:14 GMT

Riskworks114 really not sure what you are trying to achieve here, could you please provide a bit more context?

Re: SQL Date format not recognized in Paginated report

Riskworks114 — Mon, 16 Sep 2024 19:06:05 GMT

Yes, absolutely. A report that I am moving from RRSR to PBI has a query that includes a date conversion for Date data type to YYYYMMDD format with: convert(VARCHAR (10),ThruDt , 112). The data for this column is from the parameter @ThruDt which is part of a larger query that populates a tep table. This works in SSRS and in SSMS. In PBI the format qualifier is ignored, and the date displays as a normal date type. I've tried severall things, including adding a date column populated with GETDATE() which the format worked.

I added a data set with only the parameter and getdate columns and that works. It's kind of strange that the format is not working in the query that is pulling data from a database along with a parameter column where the sql format is ignored. I HTH. Thx.