How to change Date Time Picker formats

[Nevillelajru]

I have a datetimepicker which reads a date as dd-mm-yyyy. the sql im using(sql server 2008) is only reading mm-dd-yyyy. i am having a problem with changing the format of how the date is saved. i put a catch to the code and this came up

System.Data.OleDb.OleDbException: Conversion failed when converting date and/or time from character string.
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at WindowsFormsApplication1.Customer.Add() in C:\Users\Neville\Desktop\ThunderBolt Technologies Ltd\ThunderBolt Technologies Ltd\Customer.cs:line 53

when i run the program the date is represented as follows: 20/3/2011 but i want the month to come first... is this coming from sql server settings? i am using Visual C# 2008 Express Edition

Thanks

Neville

P.S. i could help some fast respnses as this is an assignment for two months and this is just the start... :/ thanks again

[Mitja Bonca]

Are you inserting into database, ot selecting from it? Regarding on your code (when using ExecuteNonQuery() method) you are inserting.

You have to change your format that will suit to the database` dateTime column format. You can do it like:

DateTime date = dateTimePicker1.Value.Date;
string sDate = date.ToString("dd-MM-yy", System.Globalization.CultureInfo.InvariantCulture);
DateTime dateInsert = Convert.ToDateTime(sDate); //use this dateInsert as parameter to insert into DB.

Mitja

[Nevillelajru]

yes i am inserting into the database. i put your code in the program and change the datatype of the sql as datetime instead of date. (there is also datetime2(7) which i dont know what it is yet) a different error came up this time...

System.Data.OleDb.OleDbException: The statement has been terminated.
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at WindowsFormsApplication1.Customer.Add() in C:\Users\Neville\Desktop\ThunderBolt Technologies Ltd\ThunderBolt Technologies Ltd\Customer.cs:line 53

line 53 is the the line where the command.EexecuteQuery(); is...

[Nevillelajru]

hey problem solved... the main problem was coming from my time settings... mine are the opposite of what i am trying to save myself so when i was trying to send the date, if the day of the month was larger than 12 then it would give me an error. i realized this when you gave me the code where it said dd-MM-yyyy and mine was trying to be saved the opposite so i went to clock settings and made the arrangements... sry for the thread and thaniks for your help :)

[Mitja Bonca]

Anytime. Just dont forget to close the thread and mark is as answered.
best regards,
Mitja