Forum Discussion

Annemie19's avatar
Annemie19
Icon for Helper II rankHelper II
5 years ago
Solved

POwe Bi Report Builder - Date/time Conversion failed

Hi There, 

 

I'm starting to use Power Bi report builder. When I try to view my report I keep on getting the error below.

 

 

My query is shown below. As soon as I replace the @ProjEndDate and @ProjStartDate with '2020-03-31', '2020-04-15' respectively the report works. 

 

SELECT
d.[DealID]
,d.[DealNumber]
,d.[PreviousDealNo]
,d.[NextDealNo]
,d.[PayMethod]
,d.[PayBranch]
,d.[PayAccountNumber]
,d.[NoOfShares]
,d.[NominalAmount]
,CONVERT( date,d.[IssueDate],103) As Issue_Date
,CONVERT( date,d.[RedemptionDate],103) As RedemptionDate
,d.[SecurityDescription]
,d.[CertifiecNos]
,d.[CertificateHeldWhere]
,d.[TypeOfClass]
,d.[CaptureDate]
,CONVERT( date,d.[RiskDate],103) As RiskDate
,CONVERT( date,d.[CreditDate],103) As CreditDate
,CONVERT( date,d.[ManagementDate],103) As ManagementDate
,CONVERT( date,d.[ReleaseDate],103) As ReleaseDate
,CONVERT( date,d.[ProjectionDate],103) As ProjectionDate
,d.[UserID]
,d.[NetCapitalChanges]
,d.[CurrentNominalAmount]
,d.[PayBranchCode]
,d.[PayAccountHolder]
,d.[DealType]
,d.[BankName]
,d.[LastInterestType]
,s.Name AS SubsidiaryName
,s.RegNo AS [Subsidiary RegNo]
,cp.Name AS CounterpartyName
,cp.CTitle1
,cp.CName1
,cp.CSurname1
,cp.CSurname2
,cp.PAddress1
,cp.PAddress2
,cp.PAddress3
,cp.PPostCode
,CONVERT( date,ProjStartDate,103) AS ProjStartDate
,CONVERT( date,ProjEndDate,103) AS ProjEndDate
,ProjectedAmount

FROM [InfoMart_FinancialProducts].[dbo].[VwPrefSharesDeal] d

JOIN [dbo].[VwPrefSharesSubsidiary] s on s.SubsidiaryHashKey = d.SubsidiaryHashKey
JOIN [dbo].[VwPrefSharesCounterparty] cp on cp.CounterpartyHashKey = d.CounterpartyHashKey
JOIN [dbo].[VwPrefSharesCashProjection] c on c.DealHashKey = d.DealHashKey

WHERE DealID =@DealID
AND CONVERT( date,ProjStartDate,103) = @ProjStartDate
AND CONVERT( date,ProjEndDate,103)= @ProjEndDate

 

Can someone please assist with this error?

 

Kind Regards,

Annemie

3 Replies

    • Annemie19's avatar
      Annemie19
      Icon for Helper II rankHelper II

      amitchandak  - Are you referring to the CONVERT( date,... it just added this before because I thought that it might be the problem. But even without the CONVERT( date,.. it still gives the same error. 

    • Annemie19's avatar
      Annemie19
      Icon for Helper II rankHelper II

      amitchandak  - I found the problem. My parameter data type was set to text and not date/time