sql date time issue

[Techno]

yup

right. Doing this project someone else has done is just complete.....lets not go there.

When the user enters a date time in the format of:

dd/mm/yyyy

SQL will complain that it cannot convert char to datetime or something and that the value given exceeds the sql datetime limit. I am guessing it is expecting mm/dd/yyyy

what is the best way to tackle this? Would I need to "switch" around the dd and mm?

[drattansingh]

Why dont you try converting the format of the date and see if it helps.

[Techno]

how do you mean? can you be more specific?

[jmcilhinney]

Date objects have no format. Format is only an issue when representing a date as a string. If you don't do that then format is never an issue. If you're getting issues with format then it means you're using strings instead of binary date objects.

How is the user entering this date? If it's a DateTimePicker, which should be the first choice, then there's never any need to even consider format in code. You simply get a DateTime object from its Value property and insert that into your database. If it's a TextBox then you need to convert the entered string to a DateTime object using Parse, TryParse or the like, then insert that DateTime object into the database. The database never sees anything but a binary date object so format is NEVER an issue.

[Techno]

hmmm ok. Thanks ill try that. I dont know why all this matters with this project - its just shockingly done.

the user is entering data in a textbox, and in the format of dd/mm/yyyy

but if we enter say 12/06/2007, SQL will find records on the dateTime field of 06/12/2007 (dd/mm swapped around)

ill try the DateTime.TryParse method but dont know how or why that will resolve the issue.

[jmcilhinney]

As I have said, format only becomes an issue when representing dates as strings. If you put a string literal into an SQL statement then SQL Server will parse that string into a date value assuming that the string is in the format MM/dd/yyyy. If you use a DateTime object then there can be no issue because a binary date object has no format. It's just a value.

Let's say that the user enters "12/06/2007" into a TextBox and you do this:

mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE DateColumn = '" + myTextBox.Text + "'";

That is a string, so the format of the date is an issue. SQL Server will ALWAYS parse that string to a date object using the form MM/dd/yyyy because SQL Server was created Americans. If you do this instead:

mySqlCommand.CommandText = "SELECT * FROM MyTable WHERE DateColumn = @DateColumn";
mySqlCommand.Parameters.AddWithValue("@DateColumn", DateTime.ParseExact(myTextBox.Text, "dd/MM/yyyy", null);

then you're specifying the format to use to parse the string to a binary date object, then you're passing that date object to SQL Server. The database doesn't have to think about format because a binary date object has no format. It's just a value.

[Techno]

thanks, thought about that and did it just before reading this