DATE is not getting updated into database using sql command

Question

I'm trying to update the date in database which at the begining is set to null, and the datatype used for date are nvarchar(50).

I've even tried nchar, date, varchar(50) datatypes in SQL Server but when updating it gives me error saying:

Conversion failed when converting date and/or time from character string.

How do I get rid of this and I want to disaply date in format 2-feb-2013 using

dd.tostring("D);

and it is not working.

SqlCommand cmd = new SqlCommand("update barcode set intime=@time,date='@date' where bcd=@bcd", con);
cmd.Parameters.Add("@cc", SqlDbType.NChar, 20).Value = c;
cmd.Parameters.Add("@bcd", SqlDbType.NChar, 20, "bcd").Value = textBox1.Text;
DateTime dd = DateTime.Now;

String ss, sss;
ss = dd.ToString("t");
sss = dd.ToString("d");

cmd.Parameters.Add("@time", SqlDbType.NVarChar, 50, "intime").Value = ss;
cmd.Parameters.Add("@date", SqlDbType.Date, 50, "date").Value = sss;
cmd.ExecuteNonQuery();

Seriously, how the hell can this be edited three times and still not be close to being correct? — Anthony Grist
– Anthony Grist, Commented Feb 20, 2013 at 13:45
@Anthony some folks make very minor edits to things like the title and ignore the rest of the post. :-( — Aaron Bertrand
– Aaron Bertrand, Commented Feb 20, 2013 at 13:47
ss,sss,cc,dd? I'd recommend you to pick more suitable names for your variables — Alex
– Alex, Commented Feb 20, 2013 at 13:50

Aaron Bertrand · Accepted Answer · 2013-02-20 13:49:47Z

1

Well, I don't see why you want to use string parameters for this, or why you care about format before sending date and time values to the database. In any case, since SQL Server can also tell time, why make the app do this at all? You can bypass all of this by setting a default value of CURRENT_TIMESTAMP for both the intime and date columns (so you don't need to even specify them when inserting), and when updating, if in fact you do need to update those columns:

UPDATE dbo.barcode 
  SET intime = CURRENT_TIMESTAMP,
      [date] = CURRENT_TIMESTAMP
  WHERE bcd = @bcd;

answered Feb 20, 2013 at 13:49

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-02-20 13:45:18Z

0

because you wrap the @date with single quotes. Parameters shouldn't be wrap with single quotes. Remove it and it will work.

UPDATE barcode 
SET    intime = @time,
       date = @date 
WHERE  bcd = @bcd

one more thing, leave the format of the date as is. Only convert the date during projection (select)

answered Feb 20, 2013 at 13:45

John Woo

265k70 gold badges509 silver badges500 bronze badges

5 Comments

Yuvi M Over a year ago

wil i get the format as i mentioned before just by using date datatype?

John Woo Over a year ago

@YuviM no. Dates should be save as date on the database. If you want the date to be formatted, do it on SELECT statements or on the application level. You will have a hard time on searching or manipulating dates if you have this format: 2-feb-2013 because it you will use varchar for that. The format of date on the database doesn't matter at all because clients will not be looking on the database. They're interested on the application.

Yuvi M Over a year ago

i have prepared a login and logout form so its necessary for me to format date

John Woo Over a year ago

question, what's the data type of the date column in your table?

John Woo Over a year ago

then pass the variable dd on the value of the paramater cmd.Parameters.Add("@date", SqlDbType.Date, 50, "@date").Value = dd;

Collectives™ on Stack Overflow

DATE is not getting updated into database using sql command

2 Answers 2

Comments

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related