String was not recognized as a valid datetime

Question

1.00/5 (1 vote)

See more:

hi every body, I wrote DateTime d = DateTime.Now; in visual studio 2012 but it returns 2017-11-22 14:14:14 nm. and generate error.
string was not recognized as a valid datetime.
I am using window 10 with date and time format
as ff.
short date=M/d/yyy
long date=dddd,MMMM d, yyyy
short time =h:mm tt
long time=h:mm:ss tt.

may I get help please?

What I have tried:

DateTime d = DateTime.Now;

Posted 22-Nov-17 13:14pm

Tom G/mariam

Updated 22-Nov-17 14:33pm

Add a Solution

Comments

phil.o 22-Nov-17 19:41pm

You are talking about a string but all you show is a code creating a DateTime value. Better show the part where you try to get a DateTime from a string.

Tom G/mariam 22-Nov-17 20:04pm

hi phil.o
here is my code that generate error. conversion failed when converting date and / or time from caracter string

Tom G/mariam 22-Nov-17 20:01pm

DateTime _date =Convert.ToDateTime( DateTime.Now);

SqlCommand command2 = new SqlCommand("SELECT date,acno,machinenumber,modified FROM CHECKINCHECKOUT where acno = '" + sdwEnrollNumber + "' and modified = 0 and machinenumber = '" + iMachineNumber + "' and date = '" +Convert.ToDateTime( _date) + "' ", con);
con.Open();
SqlDataReader reader2 = command2.ExecuteReader();

while (reader2.Read())
{

isde = true;
}

reader2.Close();
con.Close();

F-ES Sitecore 23-Nov-17 4:55am

You need to use explicit unambiguous date formats. SQL always understands the yyyy-MM-dd format so use that, or use dd MMM yyyy. That means using code like

and date = '" + _date.ToString("yyyy-MM-dd") + "' ...

however that will only work if "date" in SQL is a date field, if it is a string field then you need to use the exact format, or if the field has a time component you need to make that match also.

That's just some of the issues with your existing code, but it's for information purposes only, you should use parameterised queries as suggested below and that will handle a lot of these issues for you.

1 solution

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)

phil.o · Accepted Answer · 2017-11-22T14:33:00

Solution 1

DateTime.Now already gives you a valid DateTime value. Why are you trying to convert it, twice?

Moreover, you should not build SQL queries by concatenating strings like you do; you should use parameterized queries instead.
Protect Your Data: Prevent SQL Injection[^]

Finally, your datetime fields in your database should have a datetime type, not a string (or varchar/nvarchar) type. The way you wrote your query (enclosing the DateTime value between single quotes) makes me think you are storing datetimes as strings, which is a bad practice.

You could try:

C#

DateTime _date = DateTime.Now;

// Replace your string concatenations with proper SQL parameters specifications
SqlCommand command2 = new SqlCommand("SELECT date, acno, machinenumber, modified FROM CHECKINCHECKOUT WHERE acno = @sdwEnrollNumber AND modified = 0 AND machinenumber = @iMachineNumber AND date = @_date", con);

// Add required parameters to your command, along with their respective values
command2.Parameters.AddWithValue("@sdwEnrollNumber", sdwEnrollNumber);
command2.Parameters.AddWithValue("@iMachineNumber", iMachineNumber);
command2.Parameters.AddWithValue("@_date", _date);

// Finally open the connection and perform the query
con.Open();
SqlDataReader reader2 = command2.ExecuteReader();
// ...
reader2.Close();
con.Close();

Hope this helps. Kindly.

Edit:
Trying to fix the format issue, you can try:

C#

string formattedDateTime = _date.ToString("yyyy-MM-dd hh:mm:ss");

Posted 22-Nov-17 14:33pm

phil.o

Updated 22-Nov-17 20:58pm

v2

Comments

Tom G/mariam 22-Nov-17 23:45pm

I tride this code. but not solved. when I trace the value of _date, it shows me
2017-11-22 14:14:14 nm. what doese mean 'nm'. I need only 2017-11-22 14:14:14. it works fine on one computer but generate on onather computer. ths shows me the problem is date format configration. any healp please

phil.o 23-Nov-17 2:50am

If the datetime format is correct on one computer and not in another one, then I suspect the issue is in the second computer, not in the code. I searched for nm about datetimes and timezones, and the only result I found was the timezone code for New Mexico. I don't have any clue about the meaning of this nm suffix.
Could it be a time specifier for Amharic, Oromo or Tigrinya culture?
Please see my edited answer for trying to display the datetime in a normaized format.

Tom G/mariam 23-Nov-17 5:54am

thanks alot phil.o. you solved it. when I use your code
DateTime _date = DateTime.Now;

// Replace your string concatenations with proper SQL parameters specifications
SqlCommand command2 = new SqlCommand("SELECT date, acno, machinenumber, modified FROM CHECKINCHECKOUT WHERE acno = @sdwEnrollNumber AND modified = 0 AND machinenumber = @iMachineNumber AND date = @_date", con);

// Add required parameters to your command, along with their respective values
command2.Parameters.AddWithValue("@sdwEnrollNumber", sdwEnrollNumber);
command2.Parameters.AddWithValue("@iMachineNumber", iMachineNumber);
command2.Parameters.AddWithValue("@_date", _date);

// Finally open the connection and perform the query
con.Open();
SqlDataReader reader2 = command2.ExecuteReader();
// ...
reader2.Close();
con.Close();

....really tahnk you

Tom G/mariam 23-Nov-17 6:09am

Hi phil.o, I have datatable named as shifttable. I want to find out Rows by passing datetime parameters but it generate the same error as before that is conversion failed . what I need is how to pass parameter for the datatable. may I gate help please.
here is my code
DateTime daterange= DateTime.now
DataRow[] foundrows = shifttable.Select("EMPLOYEEID ='" + empid.ToString() + "' and STARTDATE<='" + daterange + "' and ENDDATE>='" + daterange + "'");

phil.o 23-Nov-17 6:18am

Again, do not create SQL queries like that by concatenating string fields; use parameterized queries like I showed you. This will clear a lot of issues.

Tom G/mariam 23-Nov-17 6:26am

ya ya sir, may I get sample code how to pass parameters for a data table and get rows by that criteria. thank you in advance.

phil.o 23-Nov-17 8:13am

I already gave you an example from the code in your question.
Please open a new question for this particular issue. There are things to say about your second code example, but it's not convenient to do this in comments of an already answered question.

Tom G/mariam 23-Nov-17 8:36am

Hi every one, I have datatable named as shifttable. I want to find out Rows by passing datetime parameters but it generate the same error as before that is conversion failed . what I need is how to pass parameter for the datatable. may I gate help please.
here is my code
DateTime daterange= DateTime.now
DataRow[] foundrows = shifttable.Select("EMPLOYEEID ='" + empid.ToString() + "' and STARTDATE<='" + daterange + "' and ENDDATE>='" + daterange + "'");