Internal Query Processor Error: The query processor could not produce a query plan

David Kjendle 20

Getting this error when adding time zones to original query

SELECT YEAR((CAST (log.LogTime AS DateTime) AT TIME ZONE {0} AT TIME ZONE {1})),{2},LPAD(MONTH((CAST (log.LogTime AS DateTime) AT TIME ZONE {3} AT TIME ZONE {4})), 2, {5})) AS Month

Dan Guzman 9,251 Reputation points

2025-01-08T19:03:08.76+00:00

I don't believe LPAD is a valid SQL Server function, although that is not the cause of the error. It might better help us help you if you include the entire original (working) query and modified version in your question with constants instead of interpolated string syntax.
Erland Sommarskog 115.5K Reputation points MVP

2025-01-08T19:05:23.2233333+00:00

I get

Msg 102, Level 15, State 1, Line 261 Incorrect syntax near '0'.

When I try your code. All those braces does not seem to be legal syntax to me.

If you want help, you need to provide more information. At minimum, we need to see the full query. If these braces are there because embedding into some client language, we need to see the that piece of the client code. That, or the actually query as captured from Profiler.

Since we don't have your tables, we may not still be able to reproduce the issue, so it could help to have the working query as well.

And don't forget to include the output of "SELECT @@version".
David Kjendle 20 Reputation points

2025-01-08T19:35:43.0366667+00:00

Sorry, didn't clean that up before posting it. We convert syntax from MySQL to MSSQL before running the query. The values like "Alaskan Standard Time" are parameters for the query

Before adding the time zone to the query it would look like:

YEAR(log.LogTime),'-',LPAD(MONTH(log.LogTime), 2, '0')) AS Month

Trying to add time zone info to the query and get the error

YEAR((CAST (log.LogTime AS DateTime) AT TIME ZONE N'Alaskan Standard Time' AT TIME ZONE N'Eastern Standard Time')),N'-',dbo.ips_LPAD(MONTH((CAST (log.LogTime AS DateTime) AT TIME ZONE N'Alaskan Standard Time' AT TIME ZONE N'Eastern Standard Time')), 2, N'0')) AS Month

I have other queries that include a CAST... that are working.

AND (CAST (log.LogTime AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone) BETWEEN ((CAST (@StartDate AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone)) AND ((CAST (@EndDate AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone)

I am just trying to add the CAST... to this

YEAR(log.LogTime),'-',LPAD(MONTH(log.LogTime), 2, '0')) AS Month

Version: Microsoft SQL Server 2019 (RTM-GDR) (KB5021125) - 15.0.2101.7 (X64) Jan 23 2023 13:08:05 Copyright (C) 2019 Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: ) (Hypervisor)
Erland Sommarskog 115.5K Reputation points MVP

2025-01-08T19:48:37.8066667+00:00

I'm still in the dark.

Can you share one working and one failing SQL Server Query? (Not what you have on MySQL.) And the full query, not fragments.

Also, what's is the code of that dbo.ips_LPAD function?

Furthermore, can you try to what you are really trying to achieve? Maybe it can be simplified?

David Kjendle 20

Hope this helps...

This is the original query as found in our log file. This query is working. You can see near the bottom the CAST ... AT TIME ZONE is working here .

Any @ are parameters that are listed in "with parameters"

SELECT

                      dbo.ips_Concat3(YEAR(log.LogTime),N'-',dbo.ips_LPAD(MONTH(log.LogTime), 2, N'0')) AS Month , 

                    institutions.Name AS OrgName,

                    CASE WHEN users.LoginName IS NULL THEN log.Username ELSE users.LoginName END AS Username,users.Realname,

                    

                    

                    CASE log.Action WHEN @LogAction1 THEN N'Upload' WHEN @LogAction2 THEN N'Download' ELSE log.Action END AS Action,

                    Count(*) AS Transfers,

                    ROUND((SUM(log.XferSize))/1048576,0) AS Sum_MB

			    FROM 

                    log 

                    LEFT JOIN institutions ON log.InstID = institutions.ID 

                    LEFT JOIN users ON log.Username = users.Username

                WHERE 

                    log.Action IN (@LogAction1,@LogAction2)

                    AND 

                    log.Error = 0

                     AND log.InstID = @InstID

                    AND (CAST (log.LogTime AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone)  BETWEEN ((CAST (@StartDate AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone)) AND ((CAST (@EndDate AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone))

                GROUP BY

			          YEAR(log.LogTime),MONTH(log.LogTime) , 

                    institutions.Name,

                    log.Username,users.LoginName,users.Realname,

                    

                    

                    log.Action

			    ORDER BY 

                      YEAR(log.LogTime),MONTH(log.LogTime) , 

                    OrgName

                    ,Username' 

					

					with parameters: InstID=Int32:2366; LogAction1=String:file_upload; LogAction2=String:file_download; CustomTimeZone=String:Alaskan Standard Time; ServerTimeZone=String:Eastern Standard Time; StartDate=String:2024-11-24T00:00:00; EndDate=String:2024-12-31T23:59:59 OK in 4ms - >0 rows.

ips_LDAP:

ALTER FUNCTION [dbo].[ips_LPAD]

(

@str nvarchar(4000),

@plen int,

@pchar nvarchar(100)

)

RETURNS nvarchar(4000)

BEGIN

DECLARE @Result nvarchar(4000)

SELECT @Result = RIGHT(REPLICATE(@pchar, @plen) + @str, @plen)

RETURN @Result

END

ips_Concat3:

ALTER FUNCTION [dbo].[ips_Concat3]

(

@p1 nvarchar(4000) = NULL,

@p2 nvarchar(4000) = NULL,

@p3 nvarchar(4000) = NULL

)

RETURNS nvarchar(4000)

BEGIN

DECLARE @Result nvarchar(4000)

SELECT @Result = ''

IF (NOT (@p1 IS NULL)) SELECT @Result = @Result + @p1

IF (NOT (@p2 IS NULL)) SELECT @Result = @Result + @p2

IF (NOT (@p3 IS NULL)) SELECT @Result = @Result + @p3

RETURN @Result

END

This is the query that fails. Our logging behaves differently for failures (params are added here)

The difference between the two is on the second line of the select where I try to add the CAST ... AT TIME ZONE. Maybe this can be simplified?

SELECT

                      dbo.ips_Concat3(YEAR((CAST (log.LogTime AS DateTime) AT TIME ZONE N'Alaskan Standard Time' AT TIME ZONE N'Eastern Standard Time')),N'-',dbo.ips_LPAD(MONTH((CAST (log.LogTime AS DateTime) AT TIME ZONE N'Alaskan Standard Time' AT TIME ZONE N'Eastern Standard Time')), 2, N'0')) AS Month , 

                    institutions.Name AS OrgName,

                    CASE WHEN users.LoginName IS NULL THEN log.Username ELSE users.LoginName END AS Username,users.Realname,

                    

                    

                    CASE log.Action WHEN @LogAction1 THEN N'Upload' WHEN @LogAction2 THEN N'Download' ELSE log.Action END AS Action,

                    Count(*) AS Transfers,

                    ROUND((SUM(log.XferSize))/1048576,0) AS Sum_MB

			    FROM 

                    log 

                    LEFT JOIN institutions ON log.InstID = institutions.ID 

                    LEFT JOIN users ON log.Username = users.Username

                WHERE 

                    log.Action IN (@LogAction1,@LogAction2)

                    AND 

                    log.Error = 0

                     AND log.InstID = @InstID

                    AND (CAST (log.LogTime AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone)  BETWEEN ((CAST (@StartDate AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone)) AND ((CAST (@EndDate AS DateTime) AT TIME ZONE @CustomTimeZone AT TIME ZONE @ServerTimeZone))

                GROUP BY

			          YEAR(log.LogTime),MONTH(log.LogTime) , 

                    institutions.Name,

                    log.Username,users.LoginName,users.Realname,

                    

                    

                    log.Action

			    ORDER BY 

                      YEAR(log.LogTime),MONTH(log.LogTime) , 

                    OrgName

                    ,Username

2025-01-08 15:28:46.489 #0D z10 DbConn.DoRead_DR: caught SqlException with number 8624 on statement 'SELECT

David Kjendle 20 Reputation points

2025-01-08T20:51:26.35+00:00

Just want to go

from:

ips_Concat3(YEAR({FieldPeriod}),'-',LPAD(MONTH({FieldPeriod}), 2, '0')) AS Month

To:

ips_Concat3(YEAR((CAST (log.LogTime AS DateTime) AT TIME ZONE 'XXX' AT TIME ZONE 'UTC')),{2},dbo.ips_LPAD(MONTH((CAST (log.LogTime AS DateTime) AT TIME ZONE 'XXX' AT TIME ZONE 'UTC)), 2, {5})) AS Month

Accepted answer

Erland Sommarskog 115.5K Reputation points MVP

2025-01-08T21:07:43.9066667+00:00
I was not able to repeat it with a single select from a table with a datetime column.

But it seems that you want to reduce the timestamp to year and month on the format YYYY-MM. A simple way to do that is

convert(char(7), LogTime, 121)

Try that and see what happens. If nothing else, the code will be slender.
Please sign in to rate this answer.
David Kjendle 20 Reputation points

2025-01-08T21:14:45.0933333+00:00

Will try that. Thanks for looking into this!

Erland Sommarskog 115.5K Reputation points MVP

2025-01-09T08:37:52.0333333+00:00

One thing I forgot to mention: No need for that function ips_concat3; there is a concat function in SQL Server as well. (And also concat_ws.)

David Kjendle 20 Reputation points

2025-01-09T15:05:29.82+00:00

Will look into that but kind of boxed in here. These queries all need to work with existing code for both MySQL and MSSQL. Trying to make these work with minimal changes changes.

Erland Sommarskog 115.5K Reputation points MVP

2025-01-09T22:17:45.8333333+00:00

Will look into that but kind of boxed in here. These queries all need to work with existing code for both MySQL and MSSQL

One more reason to use CONCAT over a user-defined function...
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Internal Query Processor Error: The query processor could not produce a query plan

0 additional answers

Your answer