SQL does not work in Power BI

[Anonymous]

Hi guys,

I hope you can help me. I have an SQL query that works fine when I run in my MS SQL Server Management Studio but when I connect Simba Athena and Power BI, I get this error when I ran the query below.

SELECT *,CASE 
              WHEN CountyAndId LIKE '[A-Z][A-Z]%' THEN SUBSTRING(CountyAndId,1, 2)
              WHEN CountyAndId LIKE '[A-Z]%' THEN SUBSTRING(CountyAndId,1, 1) 
              ELSE CountyAndId 
        END as CountyCode
FROM
(
       SELECT LTRIM(RTRIM([Registar])) as 'Registar'
                ,LTRIM(RTRIM(ma.[Main_Name])) as 'Main_Name'
                ,LTRIM(RTRIM([StatCodeMopel])) as 'Mopel_Description_En'
                ,LTRIM(RTRIM([YearM])) as 'YearM'
                ,LTRIM(RTRIM([Engs])) as 'EngsZ'
                ,LTRIM(RTRIM([Body])) as 'Body'
                ,LTRIM(RTRIM([Date1])) as 'Date1'
                ,LTRIM(RTRIM([Date2])) as 'Date2'
                ,LTRIM(RTRIM([DateS])) as 'DateS'
                ,LTRIM(RTRIM([MotorT])) as 'MotorTC'
                ,LTRIM(RTRIM([FuelType])) as 'FuelType'
                ,LTRIM(RTRIM([Tracks)) as 'Tracks'
                ,LTRIM(RTRIM([Colon])) as 'Colon'
                ,LTRIM(RTRIM([Tavan])) as 'Tavan]'
                ,LTRIM(RTRIM([stad])) as 'staddsd'
                ,LTRIM(RTRIM([Regs])) as 'Regs'
                ,LTRIM(RTRIM([emy])) as 'emyis'
                ,LTRIM(RTRIM([wng])) as 'wngr4'
                ,CASE 
                     WHEN Registar LIKE '[0-9][0-9][0-9]%' THEN SUBSTRING(Registar,4,LEN(Registar)-3)
                     WHEN RegistarLIKE '[0-9][0-9]%' THEN SUBSTRING(Registar,3,LEN(Registar)-2) 
                     ELSE LTRIM(RTRIM(Registar))
              END as CountyAndId 
         FROM Combined c, VRUMake ma
       WHERE c.Main = ma.Main_Code
       AND CONVERT(DATETIME, CASE WHEN ISDATE(Date1) = 1 THEN Date1 ELSE NULL END) BETWEEN '2022-03-01' AND '2022-04-01'
       AND Body IN ('01','02','40','41','42','76','77','78','92','96')
       AND [VehS]='N'
)d
ORDER BY CONVERT(DATETIME, CASE WHEN ISDATE(Date1) = 1 THEN Date1 ELSE NULL END)

I am wondering if the syntax is different from MS SQL and Power BI ? Is there any workaround on how to used datetime function in Power BI?

Thanks,

[jdbuchanan71]

@Anonymous 

Doest the code run in SSMS ok?  I have never run into a problem using CONVERT(DATETIME,...) in PowerBI.