How to insert or update data in datetime datafield in mssql2005

I have a textbox which displays the date as 01-May-开发者_运维百科2011 but the database coumis in format of datetime ... how to enter date in date time column of database. ..

how to wite the sqlquery for this ?

You can convert that format to a DateTime like this

   string dateString = "01-May-2011";
   string format = "dd-MMM-yyyy";
   var result = DateTime.ParseExact(dateString, format, CultureInfo.InvariantCulture);

if you're using LINQ to SQL or even ADO with a parameter of type DateTime, the conversion to a format that SQL understands will be done automatically for you.

If you're building the SQL by concatenating a string manually (not recommended!) you should try to reconvert to a string in the format 'yyyyMMdddd' (corrected as per AdaTheDev's comment, notice the single quotes). Other formats may or may not be recognized by sql depending on the language settings on both your client and your SQL Server

SQL Server is pretty good about taking in datetime values. If you pass the date as a parameter you can put quotes around it ('01-May-2011') and ignore the time. The database will automatically fill in a default time so that you don't have to worry about it.

Pass field value as nvarchar to database and use following to cast it to datetime.

Declare @dt nvarchar(20)
SET @dt = '01-May-2011'
select cast(@dt as datetime)

One thing to be aware of is that dates w/o time will be interpreted as May 1 2011 12AM. IE, without a time specified, SQL Server will always set the time to midnight. So if you have just the date as a field and you want records from May 1, you can't do

WHERE datefield = '5/1/2011'

This will find records where the datefield is May 1st Midnight. You have to do

WHERE datefield >= '5/1/2011' and datefield < '5/2/2011'

This doesn't really pertain to your question, but I've seen it trip up a LOT of people. Myself included.

Just convert it to dateTime

DateTime _ConvertedDate = Convert.ToDateTime(txtDate.Text);

this converts into datetime