My datetime column is in YYYYMMDDHHmmss format. Datasource of Grafana is SQL Server 2014 with read only access (TRIM() not valid).

How could I filter results in my query using Grafana time filter options? I thought in using $__timeFrom() and $__timeTo(), but Grafana uses 2022-01-21T06:29:28Z format or unixepoch.

My query needs to convert: 2022-01-21T06:29:28Z -> 20220121062928

[EDIT] Try 1 (it works):

 WHERE s.zeitpunkt 
  BETWEEN 
   CAST(REPLACE(REPLACE(REPLACE(REPLACE($__timeFrom(), '-', ''), 'T', ''), ':', ''), 'Z', '') AS VARCHAR(25))
    AND CAST(REPLACE(REPLACE(REPLACE(REPLACE($__timeTo(), '-', ''), 'T', ''), ':', ''), 'Z', '') AS VARCHAR(25))

Try 2 (it does not work):

WHERE s.zeitpunkt
  BETWEEN CONCAT(
            CONVERT(varchar, $__timeFrom(), 112) 
            , REPLACE(CONVERT(varchar, $__timeFrom(),108),':','')
            )
    AND CONCAT(
            CONVERT(varchar, $__timeTo(), 112) 
            , REPLACE(CONVERT(varchar, $__timeTo(),108),':','')
            )

CodePudding user response：

If you are having trouble with date format of sql server you can do a number of things to change date format. For Example SSMS and SQL Server date format are decided by Your language and region set

You can use CAST(),CONVERT() to change the format of date or set your format from ssms.

You Can follow this link Date and Time Format in SQL

• for datetime to datetimewith different formats

  set language 'British'

  cast(convert(varchar, getdate(), 103) as datetime)

This will change 2022-01-24 00:38:54.840 to 24/01/2022

CodePudding user response：

This is what finally worked: I Casted the datetime and used FORMAT() with a custom format. The other solutions I tried where a little bit slower.

WHERE s.zeitpunkt
  BETWEEN 
    CAST( 
        FORMAT(CAST($__timeFrom() AS DATETIME),'yyyyMMddHHmmss')
      AS VARCHAR)
    AND CAST(
        FORMAT(CAST($__timeTo() AS DATETIME),'yyyyMMddHHmmss')
      AS VARCHAR)