Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Support syntax for typed literal values

Present support for typed literals in T-SQL is very limited. Literals often undergo implicit conversions that are unexpected or inconsistent, and as types proliferate, things get worse.

For example, some datetime functions accept [datetime2] parameters, but these cannot be provided literally, since string parameters are converted to [datetime] for various (good) reasons. The value of REPLICATE('abc',10000) is unexpectedly only 8000 characters long. Simple values like 7 may be implicitly converted to int in one context and smallint or a decimal type in another, and the typing rules are not documented.

While CAST can be used to make conversions explicit, it is too cumbersome to use widely, and it is unclear in what context and at whate point in evaluation it will be interpreted or calculated. (This can matter for a distributed query between differently-localized servers, or if the CAST can produce a run-time error.) ANSI/ISO date/time literals have restricted formats and unambiguous meanings.

Guided by the ANSI/ISO standard where appropriate, the next version of SQL Server should provide support for date/time and other typed literal values.

1 vote
Sign in
Check!
(thinking…)
Reset
or sign in with
  • facebook
  • google
    Password icon
    Signed in as (Sign out)

    We’ll send you updates on this idea

    AdminMicrosoft SQL Server (Admin, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →
    unplanned  ·  AdminMicrosoft SQL Server (Admin, Microsoft Azure) responded  · 

    Upvotes: 56

    <=-=Jul 16 2008 6:21PM=-=>

    Hi Steve,
    Thanks for your feedback. We will consider your suggestions for future TSQL work.


    Umachandar, SQL Programmability Team

    <=-=Jul 17 2008 10:30AM=-=>

    And for date literals the only supported format should be YYYY-MM-DD with a 24 hour clock. Specifically
    there should not be different interpretation depending on language and dateformat settings. Errors should of course
    be caught at compile time!

    <=-=May 12 2014 8:27AM=-=>

    “And for date literals the only supported format should be YYYY-MM-DD with a 24 hour clock” some consideration to time zone should also be given.

    0 comments

    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
    • facebook
    • google
      Password icon
      Signed in as (Sign out)
      Submitting...

      Feedback and Knowledge Base