Change SQL Server dateformat?
-
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
-
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
Wrong forum!You should better post your question to General database forum. :)
Dotnetkanna wrote:
INSERT tDateOrder (d) VALUES ('2/28/04')
It isn't valid date,[EDIT]if the date format is dmy which means day/month/year. [/EDIT] An year contains 12 months not 28.You may consider change to query to:
INSERT tDateOrder (d) VALUES ('28/2/04')Life is a stage and we are all actors!
-
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
You shouldn't be passing strings to the database for dates. If you use parameterized queries the conversion happens for you automatically. Parameterized Queries[^] This example only shows text, but if you use the following syntax it will do all the type checking before it gets to the database.
cmd.Parameters.Add("@MyDate", SqlDbType.DateTime);
cmd.Parameters["@MyDate"].Value = yourDateTimeVariable;This is also the recommended way to do queries to prevent SQL injection attacks becuase it also handles things like single quotes and other things you would have to escape manually.