setting Dateformat for storing data column ?

Question

Hi,

In USA area date format is mm/dd/yyyy but in UK area it is dd/mm/yyyy.

Please guide how I can set UK area date format for storing data so that no conversions  may be required.

thanks

haansi<input id="gwProxy" type="hidden"><!-- Session data--></input> <input id="jsProxy" onclick="jsCall();" type="hidden" />

Answer 1

Your locale has no impact in the way the data is physically stored in the database.  Your locale only impacts the way you might display the data.

Answer 2

Presentation formating is normally left to the client application.

If you must format a date value and display in a particular maner, you can use the convert function. Also, the Windows regional/language setting may also effect how SSMS displays the date format.

---

"You cannot do a kindness too soon because you never know how soon it will be too late." -Ralph Waldo Emerson

Answer 3

Adding to Kent's and Arnie's comments a brief demo. Note that default dateformat for US English is mdy, for UK English is dmy.

USE AdventureWorks2008;
SET LANGUAGE English -- US English
SELECT GETDATE(), CONVERT(varchar,getdate()), DATENAME(dw, getdate())
-- 2010-01-25 10:38:15.410	Jan 25 2010 10:38AM	Monday
-- Dateformat mdy
SELECT CONVERT(datetime, '01/31/2010')
-- 2010-01-31 00:00:00.000

SET LANGUAGE 'British English' -- UK English
SELECT GETDATE(), CONVERT(varchar,getdate()), DATENAME(dw, getdate())
-- 2010-01-25 10:38:15.410	Jan 25 2010 10:38AM	Monday

-- Dateformat dmy
SELECT CONVERT(datetime, '01/31/2010')
/* sg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an 
out-of-range value.
*/
SELECT CONVERT(datetime, '31/01/2010')
-- 2010-01-31 00:00:00.000

SET LANGUAGE Spanish
SELECT GETDATE(), CONVERT(varchar,getdate()), DATENAME(dw, getdate())
-- 2010-01-25 10:38:15.410	Ene 25 2010 10:38AM	Lunes

SET LANGUAGE Swedish
SELECT GETDATE(), CONVERT(varchar,getdate()), DATENAME(dw, getdate())
-- 2010-01-25 10:38:15.413	jan 25 2010 10:38AM	måndag

SET LANGUAGE Hungarian
SELECT GETDATE(), CONVERT(varchar,getdate()), DATENAME(dw, getdate())
-- 2010-01-25 10:38:15.417	jan 25 2010 10:38AM	hétfő

---

Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com