Just in Chronicles

Life as a Voyage

Converting Date Format from String to DateTime with No Hassles

Reference: CAST and CONVERT (Transact-SQL)

For I18N or L10N, there are always problems on currency, date and time formatting. All different countries or cultures have their own format to represent their currency, date and time.

In order to overcome these hassles, regardless of country or culture settings, developers should consider two things – one is for the presentation part and the other is for the business logic part.

Any application regarding to support multiple languages or countries, must follow the convention belong to the culture. However, for the business logic which happens behind the scene doesn’t need to follow the convention but follow developers’ convention.

Therefore, the internal data conversion job that is independent from any culture, should be explicitly conducted. The following SQL query is as an example.


	CONVERT(DATETIME, '10/09/2009')         AS en-US,
	CONVERT(DATETIME, '10/09/2009', 103)    AS en-GB

The case above convert a string-formatted date to DATETIME format. The query will show the result following:

en-US			en-GB
----------------------- -----------------------
2009-10-09 00:00:00.000 2009-09-10 00:00:00.000

What’s the difference between two fields? In the American culture, the string is considered as 9th of October, 2009, while in the British culture, the same string is regarded as 10th of September, 2009. Now, let’s see the other case.


	CONVERT(DATETIME, '15/09/2009')         AS en-US,
	CONVERT(DATETIME, '15/09/2009', 103)    AS en-GB

This case also tries to convert a string-formatted date to DATETIME format. However, it returns the error like:

en-US			en-GB
----------------------- -----------------------
Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Because of the same reason above, in the US culture, the string is regarded as 9th of ???, 2009. However, there is no 15th month of a year. That’s why the error above is shown.

To sum up, data conversion must be careful, if it’s related to a certain cultural difference. Otherwise, it will be misunderstood.

화폐단위나 날짜포맷 바꿀 때 반드시 고려해야 할 사항.


Written by Justin Yoo

11/09/2009 at 01:51