Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Curly braces in T-SQL

I've come across the following t-sql:

SELECT {d'9999-12-31'} 

Which returns 9999-12-31 00:00:00.000.

This seems to be converting the type of the string literal to a DATETIME. I can't find any documentation on this syntax and I'm wondering if there are any variations, for example if I have a literal 1 but want to represent this in a BIGINT without using CONVERT()/CAST().

Can anyone provide any further information on this syntax? Thanks.

like image 506
Tom Hunter Avatar asked Sep 15 '11 09:09

Tom Hunter


People also ask

What is {} in SQL query?

@mario, "The curly braces are for complex variable expressions. They are interpreted by PHP, not by the SQL interface ".

What is a curly braces in programming?

In programming, curly braces (the { and } characters) are used in a variety of ways. In C/C++, they are used to signify the start and end of a series of statements. In the following expression, everything between the { and } are executed if the variable mouseDOWNinText is true. See event loop.

What is [] in MS SQL?

The square brackets [] are used to delimit identifiers. This is necessary if the column name is a reserved keyword or contains special characters such as a space or hyphen. Some users also like to use square brackets even when they are not necessary.


1 Answers

These are ODBC escape sequences. See Date, Time, and Timestamp Escape Sequences for more details.

There is also similar syntax for uniqueidentifiers

SELECT {guid '00000000-0000-0000-0000-000000000000'},

as well as procedure calls and some other constructs detailed off that link.

With regard to the rest of your question I'm not aware of any way of having an integer literal treated as a bigint or of any particular resource that lists all the ways of influencing how literals are assigned datatypes by SQL Server. Some ways are below.

;WITH cte(thing) AS ( SELECT CAST(1 AS SQL_VARIANT) UNION ALL SELECT $1 UNION ALL SELECT 1e0 UNION ALL SELECT 1.0000 UNION ALL SELECT 2147483648 UNION ALL  SELECT {ts '2011-09-15 01:23:56.123'}  UNION ALL SELECT {d '2011-09-15'} UNION ALL SELECT { t '13:33:41' }  UNION ALL SELECT {guid '00000000-0000-0000-0000-000000000000'} UNION ALL SELECT 'Foo' UNION ALL SELECT N'Foo' ) SELECT thing,         sql_variant_property(thing,'basetype') AS basetype,        sql_variant_property(thing,'precision') AS precision,         sql_variant_property(thing,'scale') AS scale,         sql_variant_property(thing,'maxlength') AS maxlength FROM cte 

Returns

thing                          basetype            precision   scale  maxlength ------------------------------ ------------------- ----------- ------ --------- 1                              int                 10          0      4 1.00                           money               19          4      8 1                              float               53          0      8 1.0000                         numeric             5           4      5 2147483648                     numeric             10          0      5 2011-09-15 01:23:56.123        datetime            23          3      8 2011-09-15 00:00:00.000        datetime            23          3      8 2011-09-15 13:33:41.000        datetime            23          3      8 00000000-0000-0000-0000-000000 uniqueidentifier    0           0      16 Foo                            varchar             0           0      3 Foo                            nvarchar            0           0      6 
like image 90
Martin Smith Avatar answered Sep 26 '22 23:09

Martin Smith