Date conversion

Im trying to save the contents of a text box into a date column in SQL server 2000. Youd think this would be straighforward, its not !

My date is formatted as dd/mm/yyyy, SQL server expects mm/dd/yyyy

how can I convert to this ? formatting it as a string still gives me an error when I convert back to a date.

My insert query uses a parameter of type DBDate, no matter what I try im getting a conversion error if dates exceed 12 in the dd part as SQL is expecting US format.

Can I convert to UTC ?? Ive tried several ways to convert the format, but they always give me a date conversion errorTry format it as yyyy/mm/dd

Cheers
Ken|||had to write a function to split the date up and swap the month and day around. Also had to change the parameter type in the insert SQL from date to char and let SQL convert it for me. Not the best way I know, but it works.|||::had to write a function to split the date up and swap the month and day around

Gosh :-) What about having it as a Date and using the formatting functions provided by .NET? Mving dates around as strings is wrong, as is using string manipulation to get a formatted output.

BTW - There is a culture independant form that SQL Server accepts REGARDLESS OF SETTINGS.

We use the following code in our EntityBroker O/R mapper to provide a country invariant date:

public virtual string EncodeDateTime (object Value) {
return String.Format ("'{0:yyyy-MM-dd HH:mm:ss}'", Value);
}

yyyy-MM-dd is the country invairant form. You will find this a little more elegant than yxour current appraoch. Note that the code also teaches you how to actually use String.Format to format a DateTime into a specific form.|||Thanks for your help, I tried your suggestion but got the following error

Syntax error converting datetime from character string.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.OleDb.OleDbException: Syntax error converting datetime from character string.

Source Error:

Line 426://try
Line 427: //{
Line 428: cmd.ExecuteNonQuery();
Line 429: //Message.InnerHtml = "Record Added<br>" + insertCmd.ToString();
Line 430://conn.Close();

this was the call to your procedure

String TicketDate = EncodeDateTime (DateInsertTextBox.Text);

public virtual string EncodeDateTime (object Value)
{
return String.Format ("'{0:yyyy-MM-dd HH:mm:ss}'", Value);
}

DateInsertTextBox contains a string in the format dd/mm/yyyy

This was the problem Ive been getting all along, I just cant seem to reformat the string in the textbox.

Please excuse my ignorance, Im very new to C# and am just learning the syntax.|||You may try the following code:

Imports System.Globalization

'set as Global variable
Dim GBformat As New CultureInfo("en-GB", True)

'set as local variable
Dim TestDate As Date
Dim StrDate As String = "29/11/2003"

TestDate = DateTime.Parse(StrDate, GBformat).ToShortDate

And then you use the "TestDate" as your parameter assignment. Good Luck!!!