sql server query error
sql-server add tag
Anonymous 2135 
```
SELECT HH.datetime,Isnull(A.int1, 0) AS INT1
FROM   dbo.dummy_float AS HH
       INNER JOIN (SELECT dbo.dummy_float.datetime,t.int1
                   FROM   (SELECT Dateadd(day, Datediff(day, 0, datetime), 0) AS
                                  DateTime,
                                          Format(Max(datetime), 'dd') AS sHour,
                                          Max(int1) - Min(int1) AS INT1
                           FROM   dbo.dummy_float
                           WHERE  ( datetime BETWEEN '20210801 00:00:00' AND
                                                     '20210914 13:00:00' )
                           GROUP  BY Dateadd(day, Datediff(day, 0, datetime), 0)
                          ) AS t
                          RIGHT OUTER JOIN dbo.dummy_float
                                        ON t.shour = dbo.dummy_float.datetime)
                  AS A
               ON HH.datetime = A.datetime
```
Top Answer
ypercubeᵀᴹ 
This condition that compares `shour` with `datetime` is the problem:

        ON t.shour = dbo.dummy_float.datetime
        
- `shour` is an `NVARCHAR` expression as it is the output of  `FORMAT()`
- `datetime` is of type `datetime`
    
It's not exactly clear what the condition is trying to accomplish there but in order to evaluate it, SQL Server is trying to convert the values in one of the two columns into the other datatype and failing, as for example an shour that has the value of `'20'` cannot be converted to datetime.
comments
transcript

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.