How to remove time from DateTime value?

Question

0.00/5 (No votes)

See more:

When I'm adding value from datetimepicker to db, it shows following error,
"Incorrect syntax near '12'."

C#

int id = 1;
Datetime idate = dtp.value.date; //it stores 13-07-2014 12:00:00 AM in idate

//query
cmd.CommandText="insert into tbl(id,idate) values("+ id +","+ idate +")";
cmd.ExecuteNonQuery();

I've set custom format of DateTimePicker to "dd-MM-yyyy".
I want to remove the time portion from 13-07-2014 12:00:00 AM

Posted 13-Jul-14 1:33am

Sneha_10

Add a Solution

Comments

[no name] 13-Jul-14 7:39am

You can't, hence the name DateTime. Use a proper parameterized query and it likely take care of itself.

Sneha_10 13-Jul-14 7:47am

That's the place where I'm stuck. I can't use parameterized query as my Execute method for execution of query is in different class. I'm passing the query as string in method of different class.you can check that through below given link.

http://www.codeproject.com/Questions/790287/How-to-update-delete-dynamically-in-DataGridView-i?arn=0

[no name] 13-Jul-14 7:54am

I fail to see how you could possibly be "stuck". That question has nothing at all to do with this question. You have a poorly constructed SQL query and you are trying to execute that query. Even if you had a different class somehow, you could still pass the parameters to that other class and construct your query. For some reason, you simply chose not to.

Sneha_10 13-Jul-14 8:10am

Of course that question has nothing at all to do with this. I just showed what my original code is. This is small problem for which I can't post the whole code. It's ok. I got the solution.
& No doubt You are much more experienced then me. But Please Be kind to Beginners' flaws.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Kornfeld Eliyahu Peter · Accepted Answer · 2014-07-13T01:46:00

You are doing the most dangerous error here, and you think removing the time part will solve your problem.
To be true to your - bad - solution, the problem is that you do not enclose date-time with quotes, so idate value can not be interpreted by SQL...
BUT! The true problem is that you are using string concatenation to pass values to SQL. This technique opens your system to SQL injections...
Let say that you send parameters from a user to your SQL. What will happening if your user writes this in the username box: ;DROP TABLE bar;--
You have to learn how to use parameterized queries[^]..

C#

cmd.CommandText="insert into tbl(id,idate) values(@id, @idate)";
cmd.Parameters.Add("@id", SqlDbType.Int).Value = id
cmd.Parameters.Add("@idate", SqlDbType.DateTime).Value = idate
cmd.ExecuteNonQuery();

OriginalGriff · Accepted Answer · 2014-07-13T01:57:00

"That's the place where I'm stuck. I can't use parameterized query as my Execute method for execution of query is in different class. I'm passing the query as string in method of different class."

If you can't use parameterised queries then you will probably have much, much bigger problems in future: SQL Injection doesn't care why it can work, it just does - and that can let users destroy your database very, very simply - you should strongly consider reworking the class to use parameterised queries as a high priority.

But you can do it:

C#

string cmdText = "INSERT INTO tbl (id,idate) VALUES ('"+ id +"','"+ dtp.Value.ToString("yyyy-MM-dd") + "')";

But any string concatenation is dangerous, and should be avoided. Google for "Bobby Tables" and you will find interesting stuff...

How to remove time from DateTime value?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0