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.