Forum Discussion

Riskworks114's avatar
Riskworks114
Regular Visitor
1 year ago

SQL Date format not recognized in Paginated report

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
Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

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

    • Riskworks114's avatar
      Riskworks114
      Regular Visitor
      1 year ago

      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.