I want to convert varchar to datetime

Question

0.00/5 (No votes)

See more:

When I'm converting varchar to date time i got following error
" String was not recognized as a valid DateTime. "
i try most of the way i founded but it doesn't work.plz help me.

The code are::::

C#

public void UpdateByPermitnoAndRegno(string _orgname, string _permitno, string _permitdate, string _regno, string _regdate, int _tob)
        {
            //string date1;
            Command = new SqlCommand("Organization_UpdateByPermitnoAndOrgno", Connection);
            Command.CommandType = CommandType.StoredProcedure;


            Command.Parameters.Add("@OrgName", SqlDbType.Char).Value = _orgname;
            Command.Parameters.Add("@Permitno", SqlDbType.Char).Value = _permitno;


            Command.Parameters.Add("@Permitdate", SqlDbType.DateTime).Value = DateTime.ParseExact(_permitdate, "yyyy/MM/dd", CultureInfo.InvariantCulture);
            Command.Parameters.Add("@Orgno", SqlDbType.Char).Value = _regno;
            Command.Parameters.Add("@Orgdate", SqlDbType.Char).Value = Convert.ToDateTime(_regdate).ToLongDateString();
            Command.Parameters.Add("@Tob", SqlDbType.Char).Value = _tob;

            Connection.Open();
            Command.ExecuteNonQuery();
            Connection.Close();
        }

Posted 24-Oct-14 5:00am

Member 11151776

Updated 24-Oct-14 6:01am

PIEBALDconsult

v2

Add a Solution

Comments

AnvilRanger 24-Oct-14 11:20am

And what is the value of _permitdate you are trying to parse?

Member 11151776 24-Oct-14 11:51am

Thank for your reply
I want to parse data like that "(28-1-89)"
this is the data i want to parse but i used substring following
*(Select substring(convert(varchar(9),orgdate,111),3,LEN(orgdate)-1) from Temp_Organization where permitdate='(29.3.89)')*
then that output 29.3.89 and then i want to split this but replace format like yyyy/MM/dd but i don't know how to split.plz help me

Sergey Alexandrovich Kryukov 24-Oct-14 12:08pm

First of all, don't use varchar to store time.
—SA

Member 11151776 24-Oct-14 12:17pm

thank for u advice
what should i use i want to change that varchar to datetime but in these table there are 3840 datas how can i change.i think it must be crazy so i have to try to have that i want.so if u know some good idea plz share me.

Sergey Alexandrovich Kryukov 24-Oct-14 12:22pm

Well that's bad; the data bases was screwed up in first place. Then you really need to parse the string, which is not a problem.
—SA

Member 11151776 24-Oct-14 12:28pm

i think before parse that string i have to use separator to split it.And then i should convert to datetime but i have no idea how to do it and i got headache about it.

Sergey Alexandrovich Kryukov 24-Oct-14 12:58pm

In SQL or in .NET?
How the string data looks, what format? What's the problem, actually? You don't have to have separators...
—SA

Member 11151776 25-Oct-14 0:48am

in .NET,the string data look like"(16.6.89)" just like that.

Sergey Alexandrovich Kryukov 25-Oct-14 1:45am

Then you need to use System.DateTime.ParseExact or System.DateTime.TryParseExact, with format string...
—SA

Member 11151776 25-Oct-14 10:16am

i use that too but it still doesn't work.i don't know how to do it

Sergey Alexandrovich Kryukov 25-Oct-14 23:26pm

Perhaps you need to start with reading documentation on all those methods and format strings...
—SA

Member 11151776 25-Oct-14 10:16am

yes

Member 11151776 24-Oct-14 12:22pm

firstly i want to tell what I'm doing.There are two table there r 3840 datas is table1 and another table have 1600 datas.i want to insert all of datas from table1 to table2 but some datas r same so i have to check if there is same data in table2 let do update and if not insert table2.that I'm doing so plz help me.

2 solutions

Solution 2

I do not see what version of MS SQL server...
If you use MS SQL server 2005 and higher, you can hadle errors on server side by using TRY... CATCH...[^].

On client side, i'd suggest to use the same culture as sql server uses, without any formatting.
Have a look here: Formatting Date and Time for a Specific Culture[^]

Another way is to use SET DATETIME FORMAT[^] command.

SQL

SET DATETIMEFORMAT ymd;
SELECT <field_list>
FROM <table_name>
WHERE DateTimeField BETWEEN @startdate AND @enddate</table_name></field_list>

Posted 24-Oct-14 9:40am

Maciej Los

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)

OriginalGriff · Accepted Answer · 2014-10-24T05:21:00

Most likely, it's not the code: it's the input you are passing to the code!
I assume the error occurs on this line:

C#

Command.Parameters.Add("@Permitdate", SqlDbType.DateTime).Value = DateTime.ParseExact(_permitdate, "yyyy/MM/dd", CultureInfo.InvariantCulture);

And I'd start by looking at exactly what you are handing it in the form of _permitdate - if it doesn't exactly match the format you specify: yyyy/MM/dd then you will get the exception. And that includes any other characters in the string!

So use the debugger: put a breakpoint on the line, and when your code hits it, look at what the variable contains. If it doesn't match exactly, then you need to look at where it came from!