This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(204.8, 70%, 29%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EDK%3C/text%3E%3C/svg%3E)
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
Additional SQL Server features and topics not covered by specific categories
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.
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".
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)
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?
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)
AS
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)
AS
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
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
Answer accepted by question author
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.
Will try that. Thanks for looking into this!
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.)
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.
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...