If we insert a datetime string value like ‘8/27/2010 12:00:00 AM’ into a datetime field, it works. However, the Australian date format ‘27/8/2010 12:00:00 AM’ will lead to an invalid partial datetime format.
It’s found that Local ID (LCID) added to the connection string does change the database property Locale.
Code: SqlCeEngine engine = new SqlCeEngine(this.connectionString + ";Locale Identifier=3081");
engine.CreateDatabase();
LCID values are assigned by Microsoft. For example:
English - United States, 1033
English - United Kingdom, 2057
English - Australia, 3081
However, database by default still accepts “yyyy-MM-dd” format. The datetime values in other formats inserted from code behind are converted to this format.
Code: string strInsertSql = string.Format("INSERT INTO CALENDAR (CALENDAR_DATE) " + "VALUES (‘{0}’)", aDay.Date.ToString("yyyy-MM-dd"));
The following the Australian date formats are also accepted.
Code:INSERT INTO DAY VALUES ('15 AUG 2010')
INSERT INTO DAY VALUES ('15 AUG 10')
Other date formats supported
Code:INSERT INTO DAY VALUES ('20100815')
INSERT INTO DAY VALUES ('100815')
INSERT INTO DAY VALUES ('08/15/2010')
INSERT INTO DAY VALUES ('08/15/10')
INSERT INTO DAY VALUES ('AUG 15 2010')
INSERT INTO DAY VALUES ('AUG 15, 2010')
INSERT INTO DAY VALUES ('AUG 15, 10')
INSERT INTO DAY VALUES ('AUGUST 15, 2010')
Related:
Help! SQL insert DateTime as float - Error converting data type varchar to floatHow to get time from a datetime field in sql server CEThe conversion of a char data type to a datetime data type resulted in an out-of-range datetime
