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.
Upvotes: 56<=-=Jul 16 2008 6:21PM=-=>
Thanks for your feedback. We will consider your suggestions for future TSQL work.
Umachandar, SQL Programmability Team
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!
“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.