Question

Quarter and quarter_of_year timeframes are not compatible with MSSQL

  • 4 December 2015
  • 6 replies
  • 123 views

Userlevel 2

Hi Looker team, I was looking forward to these new quarter timeframes, but they don’t work in MSSQL! The reason is that you guys are generating SQL that uses the CONCAT function, which doesn’t exist in MSSQL’s world - you just use +.


This is the error our database returns when I try to create looks using the new timeframes:

The Microsoft SQL Server database encountered an error while running this query.

‘CONCAT’ is not a recognized built-in function name.


Hope you can get this fixed soon 😞


6 replies

Userlevel 5
Badge

Hey Drew - what version of MS SQL are you using? CONCAT() seems to be a supported function in MS SQL (see here).

Userlevel 2

SELECT @@VERSION returns: Microsoft SQL Server 2008 R2 (SP1) - 10.50.2550.0 (X64) Jun 11 2012 16:41:53 Copyright © Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)


SELECT CONCAT('1','2','3') returns Msg 195, Level 15, State 10, Line 1

‘CONCAT’ is not a recognized built-in function name.

Userlevel 5
Badge

It looks like CONCAT() is supported in all MS SQL versions after 2008 (which you are on). We’ll get this fixed for older versions. But I would also recommend considering upgrading to a more recent version.

Userlevel 6
Badge

Filing a bug, we can switch concat in MSSQL to use ‘+’ so it works with previous versiosn.

Userlevel 2

Thanks guys! We’re making plans to migrate to SQL Data Warehouse on Azure… hopefully sometime in 2016. Looks like you’ve made great progress on supporting that so the timing is good.

Userlevel 5
Badge

Hey Drew - I just wanted to let you know this has been fixed! This will be fixed in the next release (Looker 3.36), but we made a patch for Looker 3.34. Let support know if you want this patch!

Reply