May 20, 2010 at 2:50 am
Hi,
By default sql server will accept date format of mdy.is it possible to change the format to dmy.
SET DATEFORMAT dmy
GO
INSERT tDateOrder (d) VALUES ('31/1/04')
GO
INSERT tDateOrder (d) VALUES ('2/28/04')
by executing the above statement im getting the below error.
(1 row(s) affected)
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.
The statement has been terminated.
is it possible to change the database format as dmy permanently? and also it should change only for this particular database.
Regards
Kanna
May 20, 2010 at 3:31 am
Kannbiran, when u change the dateformat to DMY, the insert statement must also follow suite and contain rows like '01-Mar-2010' or '30-03-2010' etc..
May 21, 2010 at 6:04 am
The date format used depends on a number of settings (see Books Online Date and Time Data Types and Functions (Transact-SQL) for the details).
There isn't a default date format for a database, but it is set to a value consistent with the current language. There is a server-wide default language, and also a default language for each login. See ALTER LOGIN and 'default language' - again in Books Online.
The relationship between date format and language can be seen in the compatibility view sys.syslanguages.
For example, the following languages all default to use DMY:
German
French
Danish
Spanish
Italian
Dutch
Norwegian
Portuguese
Finnish
Czech
Polish
Romanian
Slovak
Slovenian
Greek
Bulgarian
Russian
Turkish
British English
Estonian
Brazilian
Arabic
Thai
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply