Date Format

Question

Hi  thanks to all for my previous question on data insertion error. this time I have a problem with the format returned by toString() in windows forms; This Sample code returns 01/01/0001 regardless of the data entered. strInsert += t_ID.Text+ ; strInsert += t_Dates.Text+); OleDbCommand inst = new OleDbCommand(strInsertmyConn) ; //Execute the statement inst.ExecuteNonQuery() ; statusBar.Text=Data Added to Database  ; //reset all the textboxes int i=int.Parse(t_ID.Text); DateTime ddt = new DateTime(); i++; t_ID.Text=i.ToString() ; // this format the same as shortDateTime.tostring() t_Dates.Text = ddt.ToString(dd/MM/yyyy); statusBar.Text=Connected - Now you can Add records; myConn.Close() ;

djohncon · Answer

Hi all and thanks for all your comments. I have found the problem? this format works but it needs single quotes as sujested but the insert statement hates single quotes or completely ignores them. with this problem of single quotes I had to enbed them in the Textbox with escape \ before inserting data. This works and accepts the formatting. Thanks again kamean

tsjones6867415 · Answer

Please allways use the ISO datestring format when writing inline sql. An ISO date looks like this: 2005-01-31 and datetime like this: 2005-01-31 17:35 or this: 2005-01-31 17:35:37 Reasons: 1) 100% unambiguous 2) Standard across most databases 3) .Net will allways recognise an ISO date string correctly c# example: DateTime myDateTime = Convert.ToDateTime(2005-01-31); DateTime myDateTime = Convert.ToDateTime(2005-01-31 23:45); DateTime myDateTime = Convert.ToDateTime(2005-01-31 23:45:37); 4) MS SQL will allways recognise it as well sql example: print cast('2005-01-31' as datetime); print cast('2005-01-31 23:45' as datetime); print cast('2005-01-31 23:45:37' as datetime); 5) And lastly although slightly redundant - because it is the ISO standard. To convert a .Net DateTime into an ISO datestring use the following formatting: string ISODateString = myDateTime.ToString(yyyy-MM-dd); string ISODateString = myDateTime.ToString(yyyy-MM-dd HH:mm); string ISODateString = myDateTime.ToString(yyyy-MM-dd HH:mm:ss); So your SQL example will look like this: string sql=INSERT INTO mytable(date1string1number1) values('2005-12-31''something'5);

Andrzej Wegierski · Answer

Dates (and strings) should be in single quotes in sql (insert update ... ) statements. Proper format means: database format. For instance: string sql=INSERT INTO mytable(date1string1number1) values('12/31/2005''something'5);

djohncon · Answer

Hi thanks for your answer. I agree that it should be in single quote but the insert statement does not work with single quotes. The ToString format (dd/MM/yyyy) being a string needs double quotes. I have also tried DateTime.Parse for Example System.DateTime ddt = DateTime.Parse(t_Dates.TextSystem.Globalization.CultureInfo.CreateSpecificCulture(en-AU).DateTimeFormat); ddt.ToString(dd/MM/yyyy); I have used the date format for the South Pacific Region. I still get the same output into the MS Access DB Date 31/12/1899. Any more clues or if there is some samples of dates insertion using DAO OLEDB using MS Access.

Andrzej Wegierski · Answer

Dates should be in single quotes in sql statetements (and in proper format)