question

Jackson1990-7147 avatar image
0 Votes"
Jackson1990-7147 asked LanHuang-MSFT commented

Issue to date time field

Hi,
I get the issue below

String was not recognized as a valid DateTime. at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)

When I have the value like

7/1/2021 12:00:00 AM

to the field.

Here is of the code the line leading to the issue.


224146-image.png


dotnet-csharpdotnet-aspnet-webpages
image.png (8.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
0 Votes"
karenpayneoregon answered

I would recommend using DateTime.TryParse to ensure there is a proper DateTime.

Get user input, assert text is a DateTime and if so pass to a method, in this case in the second code block the data operation is inserting a new record but the same applies for updates too.

 if (DateTime.TryParse(TextBox1.Text.Trim(), out var dateTime))
 {
     var (success, exception, id) = DataOperations.AddNewRecord(dateTime);
     if (success)
     {
         // we can use id var
     }
     else
     {
         // failure - log error using exception var about
     }
 }
 else
 {
     // not a valid date time, tell the user
 }

Backend code

 public class DataOperations
 {
    
     private static string _connectionString =
         "Data Source=.\\sqlexpress;Initial Catalog=Examples;Integrated Security=True";
    
     public static (bool success, Exception exception, int id) AddNewRecord(DateTime dateTime)
     {
         using var cn = new SqlConnection { ConnectionString = _connectionString };
         using var cmd = new SqlCommand { Connection = cn };
         cmd.CommandText = "INSERT INTO dbo.Table1 (SomeDateTime) " +
                           "VALUES (@SomeDateTime);" +
                           "SELECT CAST(scope_identity() AS int);";
    
         cmd.Parameters.Add("@SomeDateTime", SqlDbType.DateTime).Value = dateTime;
    
         try
         {
             cn.Open();
             return (true, null, Convert.ToInt32(cmd.ExecuteScalar()));
    
         }
         catch (Exception ex)
         {
             return (false, ex, -1);
         }
     }
 }



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RafaelDaRocha avatar image
0 Votes"
RafaelDaRocha answered RafaelDaRocha edited

ParseExact would be expecting a leading 0 on dd/MM. try d/M/yyyy.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I still get the same issue after I've applied your mentioned change.

0 Votes 0 ·

Have you tried splitting the string on the whitespace and select the first part of the split?
I don't think trim would work in this case to get just the date, and I'm guessing that's what you're looking for.

0 Votes 0 ·
LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered LanHuang-MSFT commented

Hi @Jackson1990-7147,

String was not recognized as a valid DateTime

As the error says, the string is not a valid datetime.
Your format string is missing the time format and AM/PM deisgnator, and the day and month are reversed and the numbers don't match.
The correct one should be: M/d/yyyy hh:mm:ss tt

  //String = "7/1/2021 12:00:00 AM";  
 cmd3.Parameters.Add("@vaild_from", SqlDbType.DateTime).Value = DateTime.ParseExact(TextBox1.Text.Trim(), "M/d/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);
     //String = "07/01/2021 12:00:00 AM";  
 cmd3.Parameters.Add("@vaild_from", SqlDbType.DateTime).Value = DateTime.ParseExact(TextBox1.Text.Trim(), "MM/dd/yyyy hh:mm:ss tt", CultureInfo.InvariantCulture);

Best regards,
Lan Huang


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
I adjusted the code to be the following and it is still having the same issue.
224238-image.png


0 Votes 0 ·
image.png (7.3 KiB)

Hi @Jackson1990-7147,
What you wrote is completely different from what I wrote.
The date format is M/d/yyyy hh:mm:ss tt
Best regards,
Lan Huang

0 Votes 0 ·

Hi,
There is the issue below

String was not recognized as a valid DateTime. at System.DateTimeParse.ParseExact(String s, String format, DateTimeFormatInfo dtfi, DateTimeStyles style)

when the field is not with the time part there. Must we add that time part manually or is there other better way?

0 Votes 0 ·
Show more comments