Use neutral date format for DateTime parameter values

Use neutral date format for DateTime parameter values by Dan Guzman

Active

Type:	Suggestion
ID:	302236
Opened:	10/3/2007 5:22:39 AM
Access Restriction:	Public

Workaround(s)

view

Datetime parameter values are currently serialized in format "yyyy-MM-dd HH:mm:ss.fff" in SQL Trace/Profiler regardless of the DATEFORMAT setting. This may result in confusion because the meaning of datetime values in the trace text differ depending on whether the event is RPC or TSQL.

For example, with DATEFORRMAT DMY, the following trace text will result in a different datetime value depending on wheter it is an RPC or TSQL event:

exec dbo.usp_DateTime @DateTime='2007-10-03 20:21:22:023'

Details (expand)

English

Localization

Serialize RPC datetime values using a language-neutral format like "yyyy-MM-ddTHH:mm:ss.fff" or "yyyyMMdd HH:mm:ss.fff". This will help ensure users interpret trace data correctly.

Faster Development

Improved Reliability

Other (please provides details below)

Intuitive behavior.

0 attachments

Thanks for bringing this to our attention.

As you might already know, in SQL Server 2008, we're introducing a set of new date/time types such as for seperate date and time values, time zone aware and persisted datetime values and datetime values that have larger year range & fractional precision than existing datetime/smalldatetime types. The new date/time types are fully supported across server and all client providers as well as other SQL Server products (i.e. SSMS/SMO, SSIS, RS, Replication and AS).

One of the server enhancements we have added for the new date/time types is to make the ISO/SQL standard date/time format YYYY-MM-DD to be non-derterministic to the language(or date format) settings. In addition, for the existing datetime/smalldatetime types, we also made the same change in compat-level 100.

Having said this, i believe your concern should be addressed as part of the new date/time enhancement in SQL Server 2008. Please feel free to give a try of SQL Server 2008 CTP5 pre-release, and let us know if you have further questions about it.

thanks-michael