SQLServer errors

[archonic]

Describe the bug This is similar to this closed issue: https://github.com/sql-formatter-org/sql-formatter/issues/700. I'm unable to format a valid SQLServer statement. Using the demo, there appears to be no language selection that doesn't error.

Expected behavior Output should be formatted.

Actual behavior Got this error:

Error: Parse error: Unexpected "[Contact] " at line 1 column 6.
This likely happens because you're using the default "sql" dialect.
If possible, please select a more specific dialect (like sqlite, postgresql, etc).

Usage

import { format } from 'sql-formatter';
let sql_string = "WITH [Contact] AS (SELECT [ContactID], [FirstName], [LastName] FROM [dbo].[Contact])";
format(sql_string, { language: 'sql' })

• What SQL language(s) does this apply to? SQLServer

• Which SQL Formatter version are you using?

sql

I'm on 15.3.1 so I figured opening a new issue was prudent.

[archonic]

If I use Transact-SQL it no longer has a problem with the [] characters but errors on {d'2024-01 later on in my statement.

[nene]

Yes, {language: "transactsql"} is the correct setting to use for SQL Server.

I have no idea what this {d'2024-01 syntax is. Please provide a full code example.

[archonic]

Thanks for clearing that up. Here's a full example:

WITH [ContactCustomer_VW_d1c5f9_base_query] AS  (
  SELECT [Segments].[GL_Sys].[ContactCustomer_VW].[D_Customersid] AS [D_Customersid], [Segments].[GL_Sys].[ContactCustomer_VW].contactid AS contactid  
  FROM [Segments].[GL_Sys].[ContactCustomer_VW]
),  [ProductPLineMCPLineVendor_VW_1cc5f5_base_query] AS  (
  SELECT [Segments].[GL_Sys].[ProductPLineMCPLineVendor_VW].[D_Products__D_Productsid] AS [D_Products__D_Productsid], count(*) AS count  
  FROM [Segments].[GL_Sys].[ProductPLineMCPLineVendor_VW]  
  WHERE [Segments].[GL_Sys].[ProductPLineMCPLineVendor_VW].[D_Products__MCDepartmentName] IN ('POOL-ABOVE GROUND') 
  GROUP BY [Segments].[GL_Sys].[ProductPLineMCPLineVendor_VW].[D_Products__D_Productsid]
),  [D_Products_3d09b2_base_query] AS  (
  SELECT [Segments].dbo.[D_Products].[D_Productsid] AS [D_Productsid], count(*) AS count  
  FROM [Segments].dbo.[D_Products] 
  LEFT OUTER JOIN [ProductPLineMCPLineVendor_VW_1cc5f5_base_query] ON [Segments].dbo.[D_Products].[D_Productsid] = [ProductPLineMCPLineVendor_VW_1cc5f5_base_query].[D_Products__D_Productsid]  
  WHERE coalesce([ProductPLineMCPLineVendor_VW_1cc5f5_base_query].count, 0) > 0 
  GROUP BY [Segments].dbo.[D_Products].[D_Productsid]
),  [Sales_VW_d4ed36_base_query] AS  (
  SELECT [Segments].dbo.[Sales_VW].[FKCustomersid] AS [FKCustomersid], sum([Segments].dbo.[Sales_VW].[SalesDollars]) AS [SalesDollars_sum]  
  FROM [Segments].dbo.[Sales_VW] 
  LEFT OUTER JOIN [D_Products_3d09b2_base_query] ON [Segments].dbo.[Sales_VW].[FKProductsid] = [D_Products_3d09b2_base_query].[D_Productsid]  
  WHERE [Segments].dbo.[Sales_VW].[Salesdate] > {d'2024-01-01'} AND coalesce([D_Products_3d09b2_base_query].count, 0) > 0 
  GROUP BY [Segments].dbo.[Sales_VW].[FKCustomersid]
),  operation AS  (
  SELECT [ContactCustomer_VW_d1c5f9_base_query].[D_Customersid] AS [D_Customersid], [ContactCustomer_VW_d1c5f9_base_query].contactid AS contactid  
  FROM [ContactCustomer_VW_d1c5f9_base_query] 
  LEFT OUTER JOIN [Sales_VW_d4ed36_base_query] ON [ContactCustomer_VW_d1c5f9_base_query].[D_Customersid] = [Sales_VW_d4ed36_base_query].[FKCustomersid]  
  WHERE coalesce([Sales_VW_d4ed36_base_query].[SalesDollars_sum], 0) > 0
)  SELECT count(operation.[D_Customersid]) AS fws_client_customer_count  FROM operation

[nene]

So, the problem is with this {d'2024-01-01'} syntax. What is this? Is it something that SQL Server supports natively or is it something else?

It would be great if you could point me to the documentation that describes this syntax.

[archonic]

Yes, it's supported natively: https://learn.microsoft.com/en-us/sql/t-sql/data-types/date-transact-sql?view=sql-server-ver17#odbc-date-format

[archonic]

Let me know if there's anything I can do to help here. I may have time today to try my hand at supporting this syntax.

[nene]

I think a simple fix would be to just treat {...} as strings. That should be easily to do by just adding '{}' to stringTypes array: https://github.com/sql-formatter-org/sql-formatter/blob/master/src/languages/transactsql/transactsql.formatter.ts#L249

Unless there's some other syntax in SQL Server that also uses { & } characters.