SQL Date format not recognized in Paginated report

[Riskworks114]

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

[parry2k]

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

[Riskworks114]

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.