Converting time columns failed: failed to convert time column: column type "[]*string" is not convertible to time.Time

Grafana MSSQL
1

Since upgrading to version 9.0.2 under Win 11 Pro x64, I’m experiencing the following error on data tables querying MSSQL:

‘converting time columns failed: failed to convert time column: column type “*string” is not convertible to time.Time’

My query is as follows:
SELECT samp.SampleID, convert(varchar, samp.CollectedDate, 101) + ’ ’ + convert(varchar, CAST(samp.CollectedTime as TIME),8) as time,
sampAnalysis.AnalysisResult as ‘Visc@40C’,
sampAnalysis2.AnalysisResult as ‘Sulfur’,
sampAnalysis3.AnalysisResult as ‘Water’
FROM MSClims.dbo.Sample samp
JOIN MSClims.dbo.SampleAnalysis sampAnalysis ON (samp.SampleID=sampAnalysis.SampleID)
JOIN MSClims.dbo.SampleAnalysis sampAnalysis2 ON (samp.SampleID=sampAnalysis2.SampleID)
JOIN MSClims.dbo.SampleAnalysis sampAnalysis3 ON (samp.SampleID=sampAnalysis3.SampleID) WHERE
sampAnalysis.AnalysisID=(SELECT AnalysisID FROM MSClims.dbo.Analysis WHERE Analyte=‘Viscosity @ 40C’) AND
sampAnalysis2.AnalysisID=(SELECT AnalysisID FROM MSClims.dbo.Analysis WHERE Analyte=‘Sulfur’) AND
sampAnalysis3.AnalysisID=(SELECT AnalysisID FROM MSClims.dbo.Analysis WHERE Analyte=‘Water’) AND
samp.ProjectID=(SELECT ProjectID FROM MSClims.dbo.Project WHERE Name=‘FT3 - UMO Tank’)
AND samp.CollectedDate >= $__timeFrom() AND (
(sampAnalysis.AnalysisResult IS NOT NULL) OR (sampAnalysis2.AnalysisResult IS NOT NULL) OR (sampAnalysis3.AnalysisResult IS NOT NULL) )

Notes: CollectedDate is a datetime2(0), not null
CollectedTime is also a datetime2(0), null on MSSQL in the ‘MSC-LIMS’ database.

I’m not clear on how I’m supposed to cast the date/time. If I run a simplified query in MSSQL without the joins,

SELECT samp.SampleID, convert(varchar, samp.CollectedDate, 101) + ’ ’ + convert(varchar, CAST(samp.CollectedTime as TIME),8) as time
FROM MSClims.dbo.Sample samp

It returns results for ‘time’ such as 08/21/2018 12:39:13 and 09/20/2018 17:38:00

The above query and similar ones work in version 7 of Grafana. Has something changed?

Thanks,

Frank

2

Hello

What data type are CollectedDate & CollectedTime ?

3

Yosiasz:

They are both datetime2(0)

4

could be validation of data types are now stricter in new version. Because as the error indicates your time column is really varchar/string.

Can you use transformation and convert it to time

image
image1298×397 17.2 KB

1 Like
5

I renamed the field ‘Time’ from ‘time’ in all lower case. Using mixed case name in query, remedied the issue.

I was unable to select the ‘time’ lower case field to do a transformation as you had recommended above.

The only other issue I had in migrating was when I switched to new table format – font size was too large to fit all the columns so I ended up not migrating tables, since, from other threads there is not yet an option to change font size.

Thanks for your assistance.

1 Like
6

Changing my time variable to _time and then converting using the Transform in your screenshot did the trick for me, thank you! (My _time was a YYYY-MM string.)

1 Like
7

Because your time variable is string type. You can use “CAST(xxx as datetime)”. Hope can handle your bug.