sql 2012 string was not recognized as valid datetime

Question

2.50/5 (2 votes)

See more:

Dear All,

This is a similar question I have asked earlier but have not found solution to the same. But as I have tried and made several changes in code, hence posting as new question.

I am using a textbox to get date in asp.net page to sql 2012 database.
The code works fine while debugging but gives error when published.

Field type in database: datetime

Code used for conversion:

Dim varTargetDate As Date = DateTime.ParseExact(Convert.ToDateTime(txtTargetDate.Text), "dd-MM-yyyy", CultureInfo.InvariantCulture)

Short date format of machine:

dd-MM-yyyy

long date format of machine:

dd MMMM yyyy

Format: English(India)

Parameter for insert command:

daCreate.InsertCommand.Parameters.Add(New SqlParameter("@ROUTTD", varTargetDate))

Any details required from my side, pls let me know.

Thanks ..

Following command in sql to check stored format:

SELECT CONVERT(varchar(20), routtd, 107) AS Expr1 FROM ROUT

The result from command:

Oct 09, 2014

Posted 5-Oct-14 3:20am

atul sharma 5126

Updated 5-Oct-14 3:35am

v3

Add a Solution

Comments

[no name] 5-Oct-14 10:13am

Not sure why this is so confusing to you. Simply compare what you are trying to stuff into your database with what it expects to receive. If the database expects MMM dd, yyyy, is that the same as "dd-MM-yyyy"?

atul sharma 5126 5-Oct-14 11:37am

Dear Wes,
It was confusing because the user has to enter dd-MM-yyyy and the same was not accepted when the pkg is published. Whereas while debugging their was no error. So the conversion command was the issue.
Thanks for the reply.

2 solutions

Solution 2

In addition to solution 1...

To change date format on SQL server, please see: SET DATETIMEFORMAT[^]

Posted 5-Oct-14 10:06am

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)

George Jonsson · Accepted Answer · 2014-10-05T04:03:00

Solution 1

Shouldn't you use

VB

DateTime.ParseExact(txtTargetDate.Text, "dd-MM-yyyy", CultureInfo.InvariantCulture)

instead of

VB

DateTime.ParseExact(Convert.ToDateTime(txtTargetDate.Text), "dd-MM-yyyy", CultureInfo.InvariantCulture)

?
Also you have a date in a string in the format "MMM dd, yyyy" and use "dd-MM-yyyy" to parse it???

Posted 5-Oct-14 4:03am

George Jonsson

Comments

atul sharma 5126 5-Oct-14 10:19am

You are the lucky mascot for me! thanks a ton

I changed the following and it worked:
Dim varTargetDate As Date = DateTime.ParseExact((txtTargetDate.Text), "dd-MM-yyyy", System.Globalization.CultureInfo.InvariantCulture)

Please suggest how can I check before submission that the date entered is in correct format??

Thanks again..

George Jonsson 5-Oct-14 10:43am

Not sure what you mean by "before submission". Is it before the INSERT command or do you mean the Validation event of the textbox?

atul sharma 5126 5-Oct-14 11:34am

Both actually. I want to validate before insert command that the text entered in the text box is in correct date format.

George Jonsson 5-Oct-14 11:47am

You actually have validation already in ParseExact. If the date is not valid, an exception will be thrown. Then you need to take care of the exception.
You could do this in the Validating event for the textbox or in some OnClick event for a button. If you prefer you can use TryParseExact, that returns a bool instead of throwing an exception.

atul sharma 5126 5-Oct-14 12:01pm

Thanks dear

Maciej Los 5-Oct-14 16:00pm

Very good point, +5!

George Jonsson 5-Oct-14 20:40pm

Thanks. :)