How to change default systemdate from ymd to dmy

问题:

Can somebody help me to change the default systemdate from ymd to dmy? These must be for always the default format !! How?

回答1:

SET DATEFORMAT:

Sets the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings.

[Note: This is often not the way to solve the problem of interpreting dates. Datetimes should not be stored a strings if you can avoid it (use a datetime or date column instead). If you have to store in a string form, use an ISO 8601 format which is basically of the form YYYYMMDD ]

Example from MSDN:

-- Set date format to day/month/year.
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: 2008-12-31 09:01:01.123
SET DATEFORMAT dmy;
GO
DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';
SELECT @datevar;
GO
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.
GO

回答2:

It's possible to set the default date format for the current login by changing the default language.

For month/day/year:

ALTER LOGIN [MyUser] WITH DEFAULT_LANGUAGE = us_english
select CAST('01-06-2011' as datetime)
-- 2011-01-06 00:00:00.000

Or for day/month/year:

ALTER LOGIN [MyUser] WITH DEFAULT_LANGUAGE = British
select CAST('01-06-2011' as datetime)
-- 2011-06-01 00:00:00.000

You can choose from any of the languages listed in sys.syslanguages. Changes won't take effect until you login again.