Error information about "db query error: mssql: Incorrect syntax near 't2'."

Grafana MSSQL
1

code:
select $__timeEpoch(convert(varchar(7),time,120)) as time, AVG(AIFPY) as Mon_Avg_AIFPY
FROM(
select convert(date, update_time, 23) as time,AIFPY
from(
select *,ROW_NUMBER() over(PARTITION BY left(CONVERT (varchar,update_time,112),8 )order by update_time desc) as rank
from [dbo].[THT2FPY]
) t1
where rank=1 and $__timeFilter(update_time)
) t2
GROUP BY $__timeEpoch(convert(varchar(7),time,120))

Grafana version: v8.0.6
Error information: db query error: mssql: Incorrect syntax near 't2
Target to achieve: I’m trying to get a graph which can show every month’s average value of “AIFPY” (which is saved in the database: microsoft SQL).

Could you please help to modify the syntax, many thanks!

2

The generated code is producing

DATEDIFF(second, '1970-01-01', convert(varchar(7) AS time,time,120)) as time

the convert function is wonky

try this until you sort that issue

select DATEDIFF(second, '1970-01-01', convert(varchar(7),time,120)) as time, 
AVG(AIFPY) as Mon_Avg_AIFPY
FROM(
	select convert(date, update_time, 23) as time,AIFPY
	from(
		select *,ROW_NUMBER() 
over(PARTITION BY left(CONVERT (varchar(10),update_time,112),8 ) order by update_time desc) as rank
		from [THT2FPY]
	) t1
	where rank=1 and update_time BETWEEN '2023-05-15T01:27:27Z' AND '2023-05-15T07:27:27Z'
) t2
GROUP BY DATEDIFF(second, '1970-01-01', convert(varchar(7),time,120))
3

thanks for your reply, but still have the same error: db query error: mssql: Incorrect syntax near ‘t2’.
I think it may not be the error about the datediff() or $__timeEpoch(). it might be the syntax of ‘group by’ or somewhere around ‘t2’ has mistake.

BTW: the X Axes is needed by the form of every month not every day.

4

I think it is a bug, did you notice the problem is?

convert(varchar(7) AS time,time,120)

you cannot have as time in the convert function. Instead carve that part manually as

DATEDIFF(second, '1970-01-01', convert(varchar(7),time,120))

So don’t use $__timeEpoch part and only use the $__timeFilter(update_time) part

5

ah i see, i have deleted the ‘as time’ inside convert(), then there’s not error coming out. however, there is no graph, and it shows on the panel ‘data does not have a time field’

6

is there any other way to get the average value of each month and show it on the graph? the subquery in the above code can show everyday’s data on the graph, so maybe just add another query out side the subquery

7

Take that ssme query and run it in ssms. What do you get

8

if use datediff() as you suggested, in ssms the error is
“Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.”

if i change the first and last line of code to:
select $__timeGroup(time, ‘1M’,0) as time, AVG(AIFPY) as Mon_Avg_AIFPY
select convert(date, update_time, 23) as time,AIFPY
from(
select *,ROW_NUMBER() over(PARTITION BY left(CONVERT (varchar,update_time,112),8 )order by update_time desc) as rank
from [dbo].[THT2FPY]
) t
where rank=1 and $__timeFilter(update_time)
) t
GROUP BY $__timeGroup(time, ‘1M’,0)

it can calculate the average value, but there’re two problems:

  1. $__timeGroup() will change the time to Unix which is second, but it default 28 days for a month, so the calculation for some months which have 30 or 31 days will have some problems.
  2. after it change the time to Unix form, the X axes is formed in MM/DD. but i need the YYYY/MM form
9

please post DDL and DML

DDL

--we dont know your data types so please correct
create table sith(update_time datetime, AIFPY decimal(10,2))

DML

insert into sith
select '2023-05-15 01:00:00', 33.3 union
select '2023-05-15 02:00:00', 3.3 
--etc

At this point it would be trying to shoot darts in the dark without sample data. Help us help you by providing us sample data as we do not have access to your server and db

or just a simple

select '2023-05-15 01:00:00', 33.3 union
select '2023-05-15 02:00:00', 3.3 
--etc
10

thank you for your help, the problem has already solved. i just need to change the graph type from time series to bar chart, then it can use the string as X Axes. thanks again

1 Like