Support syntax for typed literal values - by Steve Kass

Status : 

 


82
0
Sign in
to vote
ID 356470 Comments
Status Active Workarounds
Type Suggestion Repros 1
Opened 7/16/2008 4:35:06 PM
Duplicates 611171 Access Restriction Public

Description

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.
Sign in to post a comment.
Posted by aptitude on 5/12/2014 at 8:27 AM
"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.
Posted by Erland Sommarskog on 7/17/2008 at 10:30 AM
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!
Posted by Microsoft on 7/16/2008 at 6:21 PM
Hi Steve,
Thanks for your feedback. We will consider your suggestions for future TSQL work.

--
Umachandar, SQL Programmability Team