sql server query error - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

sql server query error

sql-server add tag

4 years ago 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

xxxxxxxxxx
 
SELECT HH.datetime,Isnull(A.int1, 0) AS INT1FROM   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
show all 16 lines

Top Answer

4 years ago ypercubeᵀᴹ

This condition that compares shour with datetime is the problem:

    ON t.shour = dbo.dummy_float.datetime

xxxxxxxxxx
 
    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.

1 Answer