Friday 1 December 2006

Solving data time format problems between SQL Server and APP Server

Details – Source - MS Press book on SQL Server 2005
Datetime manipulation can be quite challenging. What's the correct way to express DATETIME literals? What happens when you enter a value that cannot be represented exactly—for example, '20060211 23:59:59.999'? How do you separate date and time? The following sections provide some answers to these questions.
LiteralsExpressing DATETIME literals for data entry in T-SQL is tricky business. When you need to express one, you use a character string that is implicitly convertible to DATETIME. If a string appears in a context where a DATETIME is expected—for example, as the target value of a DATETIME column in an INSERT or UPDATE statement—it will be implicitly converted to DATETIME. Also, when expressions contain operands with different datatypes, normally the highest in precedence determines the datatype of all operands. Datetime has a higher precedence than a character string. So, for example, if you compare two values, one of which is a DATETIME and the other is a character string, the character string gets implicitly converted to a DATETIME.
To add to the confusion, there are various conventions for expressing DATETIME values. The value '02/12/06' means different things to different people. When this string must be converted to a DATETIME, SQL Server will convert it based on the language settings of the session. The session's language is determined by the login's default language, but it can be overridden by using the SET LANGUAGE session option. You can also control how DATETIME literals comprising digits and separators are interpreted by using the SET DATE-FORMAT option, specifying a combination of the characters d, m, and y. For example, mdy would mean month, day, year. By the way, SET LANGUAGE implicitly sets DATEFORMAT to match the language's convention for date formatting.
So you have tools to control the way some DATETIME literals will be interpreted, but you realize that by issuing one of the aforementioned SET options, you're changing the behavior of the whole session. What if other code that will end up running in your session is supposed to be dependent on the login's default language? This consideration is especially important with international applications.
Whenever possible, I write code that is independent of any settings or switches in the system. There are two literal DATETIME formats in SQL Server that are independent of any settings. I particularly like the one of these formats that has no separators between the date portions: '[yy]yymmdd[ hh:mi[:ss][.mmm]]'. Examples of DATETIME literals in this format are '20060212', '060212', and '20060211 23:59:59.997'. The DATEFORMAT and LANGUAGE settings do not affect the interpretation of DATETIME strings in this format. If you would rather use separators between the date parts, you can use the other setting-independent format in SQL Server: 'yyyy-mm-ddThh:mi:ss[.mmm]'. An example of this format is '2006-02-12T14:23:05'. The time portion cannot be omitted when this format is used.
Another technique you can use to specify DATETIME values is to explicitly convert the character string to a DATETIME using the T-SQL function CONVERT, specifying the option style parameter in the function call. For example, if you want to use the British/French style with two digits for the year, specify style 3: CONVERT(DATETIME, '12/02/06', 3). For the full list of supported styles, please refer to Books Online, under the subject CAST and CONVERT (Transact-SQL).
At some point, you may see a date or time literal such as {d '2006-02-12'}. This is an ODBC format that can be used in some APIs. I wouldn't recommend using such literals because even though they are independent of settings, they are API dependent.

3 comments:

Anonymous said...

Nice dispatch and this enter helped me alot in my college assignement. Say thank you you for your information.

Unknown said...

ninest123 16.03
ray ban sunglasses, cheap oakley sunglasses, replica watches, michael kors outlet, oakley sunglasses, jordan shoes, prada handbags, nike outlet, nike air max, nike free, louis vuitton, tiffany and co, ugg boots, louboutin outlet, ray ban sunglasses, louis vuitton outlet, burberry outlet online, michael kors outlet, chanel handbags, uggs on sale, tiffany jewelry, louis vuitton outlet, michael kors outlet, oakley sunglasses, oakley sunglasses, louboutin shoes, prada outlet, ugg boots, ray ban sunglasses, ugg boots, burberry, replica watches, michael kors outlet, tory burch outlet, longchamp outlet, michael kors outlet, louis vuitton, michael kors, polo ralph lauren outlet, longchamp outlet, louis vuitton, polo ralph lauren outlet, louboutin, oakley sunglasses, gucci outlet, ugg boots, christian louboutin outlet, nike air max, longchamp

Unknown said...

ugg,uggs,uggs canada, canada goose, ugg,ugg australia,ugg italia, canada goose, hollister, wedding dresses, moncler, louis vuitton, canada goose outlet, swarovski, pandora charms, pandora jewelry, moncler outlet, moncler, sac louis vuitton pas cher, replica watches, louis vuitton, marc jacobs, louis vuitton, vans, moncler, swarovski crystal, pandora jewelry, toms shoes, moncler, juicy couture outlet, doudoune canada goose, karen millen, converse, lancel, canada goose outlet, coach outlet, gucci, ray ban, thomas sabo, moncler, supra shoes, canada goose uk, juicy couture outlet, links of london, barbour jackets, louis vuitton, canada goose, barbour, ugg boots uk, pandora charms, bottes ugg, converse outlet, moncler, montre pas cher, canada goose, ugg pas cher, moncler
ninest123 16.03