Curly braces in T-SQL

Question

Post reply

Curly braces in T-SQL

Igor Makedon

Ten Centuries

Points: 1235
More actions
September 20, 2005 at 6:29 am

#89285

This query (obtained by means of SQL Profiler) works as expected:
SELECT Date_Stamp FROM AHU_1
WHERE Date_Stamp >= {d '2005-06-16'} AND
Date_Stamp <= {ts '2005-07-12 23:45:00'}
It returns 2,000+ rows.
The question: What do constructions in braces mean exactly?
Is this syntax documented?
Thanks,
--Igor

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

peterhe SSChampion Points: 11469 More actions · Answer 1

These are ODBC format data type, from BOL:

ODBC Datetime Format

The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the Microsoft OLE DB Provider for SQL Server. Applications using the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times.

ODBC timestamp escape sequences are of the format:

{ literal_type 'constant_value' }

literal_type

Specifies the type of the escape sequence. Timestamps have three literal_type specifiers:

d = date only

t = time only

ts = timestamp (time + date)

'constant_value'

Is the value of the escape sequence. constant_value must follow these formats for each literal_type.

literal_type	constant_value format
D	yyyy-mm-dd
T	hh:mm:ss[.fff]
Ts	yyyy-mm-dd hh:mm:ss[.fff]

These are examples of ODBC time and date constants:

{ ts '1998-05-02 01:23:56.123' }

{ d '1990-10-02' }

{ t '13:33:41' }

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 2

Thanks, Enthusiast. Although I am not going to use it I am just curious:

Can these braces be used in any other context?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

His name is peterhe. Enthusiast is just related to his number of postings.

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 4

Igor Makedon

Ten Centuries

Points: 1235

September 20, 2005 at 8:13 am

#591344

My apologies to peterhe and to the forum.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

Not the first time it happens... and certainly not the last .

sushila SSC-Dedicated Points: 35293 More actions · Answer 6

that's right 5000 posts...oops, I mean - remi....oops, I mean one who doesn't give back NAMES given on loan....OOPS, I mean RGR'us....

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I'll let you work on that one. Fax it to me when you're done .

SQLBill SSC Guru Points: 51440 More actions · Answer 8

Igor,

Curly braces are not used in SQL Server's TSQL. They are normally seen in third-party applications such as Crystal Reports. When you view the SQL for a Crystal Report, parameter values will be inside curly braces.

-SQLBill

Igor Makedon Ten Centuries Points: 1235 More actions · Answer 9

Thank you SQLBill for your answer, but it looks like there is kind of misundersanding.

1) From Query Analyzer I issue a statement.

2) This statement contains braces.

3) SQL Server successfully executes this statement.

My question is: are there any other contexts that curly braces can be used within T-SQL (I realize that T-SQL is syntactically not even close to Java/C[++, #])?

In other words, can I be this "third party" that uses such underground(?) syntax (which is incompatible with SQL standards)?

Regards,

--Igor

noeld SSC Guru Points: 96590 More actions · Answer 10

Igor {} can be used also to call ODBC scalar functions and the {} are there for backward compatibility and yes, they WORK!

an example of such functions is:

select {fn ucase( data_column)} from table

Tools like Crystal Reports generate this kind of ODBC Compliant code and then they get automatic portability to other DB Systemes Like ORACLE,DB2, Sybase, ... any ODBC Compliant driver

Cheers!

* Noel