convering string to datetime with dateparseexact

Question

0.00/5 (No votes)

See more:

It may sound very elementary but I am having problem converting string to date time

My code is as follows:

SqlCommand com = new SqlCommand("SELECT Date FROM pD WHERE userName=@UN", con);
        com.Parameters.AddWithValue("@UN", username);
        DateTime date = DateTime.ParseExact((com.ExecuteScalar().ToString()), "MM/dd/yyyy hh:mm:ss tt", null) ;

        int i = DateTime.Compare(date, DateTime.UtcNow.AddYears(-1));
        if (1 < 0)
        {
            // code for old cases
        }
        else
        {
            //code for recent cases
        }

When I run this code in visual studio 2012, I get the error

"String was not recognized as a valid DateTime".

A single username for which I ran this code has the value in date field = 1/23/2013 10;20:00 AM

I am not able to figure out what has gone wrong with my code in the line

DateTime date = DateTime.ParseExact((com.ExecuteScalar().ToString()), "MM/dd/yyyy hh:mm:ss tt", null) ;<br />

that is giving this error

I have searched the answer on net but I am unable to solve this elementary problem
I know it is a shame but I will be ignorant till I seek help

Many many thanks for your help

Posted 24-Jan-14 13:30pm

Member 10235977

Updated 24-Jan-14 13:43pm

PIEBALDconsult

v3

Add a Solution

3 solutions

Solution 2

In this case the error may be as simple as the fact that you are using an incorrect formatting string with ParseExact: you have semi-colon where there should be a colon, and you use "MM" which looks for two digits for the month value, rather than "M" which would accept either one or two digits.

Compare these two formatting examples which will compile:

string dString1 = "01/23/2013 10:20:00 AM";
string dString2 = "1/23/2013 10:20:00 AM";

string sFmt1 = @"MM/d/yyyy hh:mm:ss tt";
string sFmt2 = @"M/d/yyyy hh:mm:ss tt";

DateTime date1 = DateTime.ParseExact(dString1, sFmt1, null);
DateTime date2 = DateTime.ParseExact(dString2, sFmt2, null);

I hope you'll take PIEBALDconsult's advice and switch to using native SQL DateTime values rather than strings since, as I understand it, using strings can present a risk of malware injection, as well as localisation issues, hoof-in-mouth disease, etc.

Posted 24-Jan-14 14:44pm

BillWoodruff

Solution 3

IF your Date column in the pD table is of Type DateTime then you can follow Solution 1
if it is off type Varchar , then write the following code

C#

string strDate = com.ExecuteScalar().ToString();

Now apply a break point on this line and check the Format how it is displayed. based on the format you can frame the string format in the ParseExact Method..

Posted 24-Jan-14 14:47pm

Karthik_Mahalingam

Comments

Member 10235977 25-Jan-14 5:27am

Thanks

solution 1 has worked well. solution 2 and 3 did not work for me, as the string is being taken from database and month 01 is written as 1 only and not as 01, as we can do when we take date string from text box.

many thanks to all for help

Karthik_Mahalingam 25-Jan-14 5:31am

welcome :)

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)

PIEBALDconsult · Accepted Answer · 2014-01-24T13:42:00

Solution 1

If the value is stored as a DATETIME (as it should be) then you don't need to do that; just cast the value:

DateTime date = (DateTime) com.ExecuteScalar() ;

If the value is stored as a string, then please reconsider -- always store dates as DATETIME (or DATE), and never as strings.

Posted 24-Jan-14 13:42pm

PIEBALDconsult