Error while trying to run a SQL query with VBA excel

Especially for complex queries, consider separating SQL and VBA and parameterizing the SQL as a prepared statement with qmark placeholders. ADO supports parameterization with the ADO command object which coincidentally you already use! This allows you to avoid any DECLARE and messy, even dangerous concatenation. Also, because of parameterization, purposely use date types and avoid any FORMAT or CONVERT needs. You can also avoid #temp1 with a single statement:

SQL (save as .sql or string in an Excel cell)

Query below uses more informative aliases and uses AS operator for column aliasing. Also, all system commands are consistently capitalized for readability. Please note the use of qmarks (?) for parameters and not @ variables. Please test query and adjust as needed.

SELECT x.[Charge_slabs_A], x.[Slab_weight_Discharged_A],
       x.[AVG_Charg_Temp_A], y.[AVG_DisCharg_Temp_B]
FROM 
   (SELECT COUNT(CASE
                    WHEN h.[FURNACE_NUMBER]=1 
                    THEN h.[slab_weight] 
                    ELSE NULL 
                 END) AS [Charge_slabs_A],
           1000 * AVG(r.[fa_weight]) AS [Slab_weight_Discharged_A],
           AVG(CASE 
                  WHEN m.[Furnace]='A' 
                  THEN m.[charge_temperature]
                  ELSE NULL 
               END) AS [AVG_Charg_Temp_A]
   FROM fix.dbo.Fce_HD_Hourly AS f
   LEFT JOIN ALPHADm.dbo.Mill_Temp_Aims AS m 
        ON DATEADD(HOUR, DATEDIFF(HOUR, 0, m.[charge_time]), 0) = f.[_TimeStamp]
   LEFT JOIN ALPHADm.dbo.reheats_hourly_data AS r 
        ON r.[start_time]= f.[_timestamp]
   LEFT JOIN alphadm.dbo.HFNCPDI h
        ON  h.[counter] = m.[mill_counter]
   WHERE f.[_TimeStamp] BETWEEN ? AND ?
     AND m.[charge_time] BETWEEN ? AND ?
   GROUP BY f.[_TimeStamp]
  ) AS x 
FULL OUTER JOIN 
  (SELECT AVG(CASE 
                 WHEN m.[FURNACE] ='B' 
                 THEN convert(real,isnull (m.[ave_disch_temp],'0')) 
                 ELSE NULL 
              END) AS [AVG_DisCharg_Temp_B],
          f.[_TimeStamp] AS [Time]
   FROM fix.dbo.Fce_HD_Hourly AS f
   LEFT JOIN Mill_Temp_Aims AS m 
        ON DATEADD(HOUR, DATEDIFF(HOUR, 0, m.[discharge_time]), 0) = f.[_TimeStamp]
   WHERE f.[_TimeStamp] BETWEEN ? AND ?
     AND m.[discharge_time] BETWEEN ? AND ?
   GROUP BY f.[_TimeStamp]
  ) AS y 
ON y.[Time] = x.[_TimeStamp]

VBA (reads in above query and binds date parameters)

Private Sub UpdateButton_Click()
    Dim oCon As ADODB.Connection, oCmd As ADODB.Command
    Dim rs As Object, SQL_1 As String
    Dim WS As Worksheet, n As Long
    
'GET DATES
    Dim StartDate As Date, EndDate As Date
    With ThisWorkbook.Sheets("A&B Sankey")
        StartDate = CDate(.Range("R2").Value)
        EndDate = CDate(.Range("T2").Value)
    End With
    
'CONNECT FUNCTION
    Set oCon = DbConnect
    Set oCmd = CreateObject("ADODB.Command")
    oCmd.CommandTimeout = 0
    oCmd.ActiveConnection = oCon
    
'READ IN SQL
   With CreateObject("Scripting.FileSystemObject")
       SQL_1 = .OpenTextFile("C:\path\to\my\SQL\Query.sql", 1).readall
   End With
   ' SQL_1 = ThisWorkbook.Sheets("MySQLSheet").Range("A1")

'EXECUTE RESULT
   With oCmd
       .CommandText = SQL_1
                
       ' BIND ? PARAMETERS IN SQL (USING adDate TYPES)
       For n = 1 to 4
           .Parameters.Append .CreateParameter("startdateparam" & n, adDate, adParamInput, , StartDate)
           .Parameters.Append .CreateParameter("enddateparam" & n, adDate, adParamInput, , EndDate)
       Next n

       ' CREATE RECORDSET
       Set rs = .Execute
    End With
    
'SHOW RESULT
    With ThisWorkbook.Sheets("-Input Data-")
        .Range("B20:CC20000").ClearContents
        .Range("B20").CopyFromRecordset rs
    End With

'CLOSE
    MsgBox "Result written to " & WS.Name & _
           "For " & StartDate & "-" & EndDate, vbInformation, "Finished"
    rs.Close: oCon.Close
    Set rs = Nothing: Set oCmd = Nothing: Set oCon = Nothing
End Sub

Function DbConnect() As ADODB.Connection
    Dim sConn As String
    sConn = "Driver={SQL Server}; SERVER=; " & _
            "UID=; PWD=; DATABASE=;"
    Set DbConnect = CreateObject("ADODB.Connection")
    DbConnect.Open sConn
End Function