Curly braces in T-SQL

  • 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

     

  • 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' }

     

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

    Can these braces be used in any other context?

     

     

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

  • My apologies to peterhe and to the forum.

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

  • 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 !!!**

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

  • 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

  • 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

  • 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

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

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